0

Let's say I have a table like this, where B is vertically merged for A=1 and A=2

A | B
------
1   f
2   o
3   
4   b
5   a
6   r

How can I look up the value of B, for A=3? The way I see it, A=2 and A=3 share a B, but the way Google Sheets's VLOOKUP seems to think of it, A=3 has no matching B. This seems like a bad design because the cell is only visually MERGING across the row but functionally you're losing data. Is there a workaround for this?

This returns nothing,

=VLOOKUP(3, A1:B6, 2)

While this returns o

=VLOOKUP(2, A1:B6, 2)
1

Assuming the values in the lookup column are numerical and in ascending order:

=ArrayFormula(LOOKUP(3,A:A/(B:B<>""),B:B))

  • Could you explain that a bit? – Evan Carroll Dec 4 '18 at 4:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.