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

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?

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

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 wrote:

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

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

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" wrote:

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 wrote:

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

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

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

You sure you used ctrl-shift-enter?

KWilde wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

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

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" wrote:

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

You sure you used ctrl-shift-enter?

KWilde wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

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

They both worked for me.

Did you change the formula?

KWilde wrote:

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" wrote:

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

You sure you used ctrl-shift-enter?

KWilde wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson


--

Dave Peterson

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

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

"Dave Peterson" wrote:

They both worked for me.

Did you change the formula?

KWilde wrote:

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" wrote:

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

You sure you used ctrl-shift-enter?

KWilde wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson


--

Dave Peterson