How do you vlookup a blank lookup value?

[PATSYS]

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.

 

[barry houdini]

Try

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

confirmed with CTRL+SHIFT+ENTER

 

[PATSYS]

It works..thanks.

 

[PATSYS]

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]

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]

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))