How do you vlookup a blank lookup value?

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
One of the values in my array is blank. Now I want to vlookup this blank i.e. =VLOOKUP("",A1:B7,2,FALSE) but it returns error #N/A.

Help.

Thanks.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
Try

=INDEX(B1:B7,MATCH(TRUE,A1:A7="",0))

confirmed with CTRL+SHIFT+ENTER

Hey man,

Is there a way to go without using array formula? The reason being that I wil using is as formula to name a range and I do not think array formulas are accepted in the "Refers to" box in naming ranges.

Thanks
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
You can "cheat" by inserting a hidden column A...

A1 = if (B1=""," ",b1)

Blanks work. "" doesn't. Or you can simply replace any "" with " " in your lookup range, and vlookup that.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Is there a way to go without using array formula? The reason being that I wil using is as formula to name a range and I do not think array formulas are accepted in the "Refers to" box in naming ranges.

You can try

=INDEX(B1:B7,MATCH(TRUE,INDEX(A1:A7="",0),0))
 

Watch MrExcel Video

Forum statistics

Threads
1,113,990
Messages
5,545,365
Members
410,679
Latest member
rolandbianco
Top