How to reference a null value? ie: =Lookup("", B2:B4, A2:A4)

[KWilde]

I'm trying to reference a null value in the lookup function and I keep
getting #NA back in return. Is there anyway I can do this?
=Lookup("", B2:B4, A2:A4) gives me #NA.

Basically in column B I want to lookup the first blank cell and want to
return the contents in column A in the same row. How can I do this?

 

[Dave Peterson]

You could use an array formula:

=LOOKUP("", B2:B4&"", A2:A4)
or
=INDEX(A2:A4,MATCH(TRUE,B2:B4="",0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[KWilde]

Thanks. Unfortunately when trying both of these it stills returns an #N/A
error.
It's like it doens't understand the null or blank cell as a value or
something.

Any other ideas?

 

[Dave Peterson]

You sure that there's a blank in B2:B4?

You sure you used ctrl-shift-enter?

 

[KWilde]

yes and yes.
when i say blank i mean that i've done nothing with the cell. meaning i
haven't typed in ="", it's just blank.

 

[Dave Peterson]

They both worked for me.

Did you change the formula?

yes and yes.
when i say blank i mean that i've done nothing with the cell. meaning i
haven't typed in ="", it's just blank.

 

[KWilde]

Oh my goodness thank you! I was changing the formula - you're right.
It works great!