Vlookup information from blank cell

[Doug]

Can anyone help please.

I wish to return the value from D4(saucer)in cells B2 and B7.
My worksheet contains a few thousand rows and "saucer" will always
correspond to a blank cell.
I have been using the formula below,but this does not recognise a blank cell.
VLOOKUP(A2,$C$1:$D$8,2,FALSE


A B C D
1 prod3 oven prod1 cup
2 saucer prod2 kettle
3 prod1 cup prod1 cup
4 prod2 kettle saucer
5 prod3 oven prod3 oven
6 prod1 cup prod2 kettle
7 saucer prod2 kettle
8 prod3 oven prod1 cup

 

[Dave Peterson]

One way around it is to put ="" in those empty cells in c1:c8. These cells will
still look empty.

Then use a formula like:
=VLOOKUP(A2&"",$c$1:$d$8,2,FALSE)

Another way:
=INDEX($d$1:$d$8,MATCH(A2&"",$c$1:$c$8&"",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.)

And you can't use the whole column unless you're using xl2007.

 

[Max]

Place in B1, then array-enter the formula ie press CTRL+SHIFT+ENTER
instead of just pressing ENTER:
=INDEX($D$1:$D$7,MATCH(TRUE,$C$1:$C$7=A1,0))
Copy B1 down to B7. Adapt the ranges to suit.

 

[Doug]

Thanks Dave,i'll try it.

One way around it is to put ="" in those empty cells in c1:c8. These cells will
still look empty.

Then use a formula like:
=VLOOKUP(A2&"",$c$1:$d$8,2,FALSE)

Another way:
=INDEX($d$1:$d$8,MATCH(A2&"",$c$1:$c$8&"",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.)

And you can't use the whole column unless you're using xl2007.

 

[Doug]

Thanks Max

Place in B1, then array-enter the formula ie press CTRL+SHIFT+ENTER
instead of just pressing ENTER:
=INDEX($D$1:$D$7,MATCH(TRUE,$C$1:$C$7=A1,0))
Copy B1 down to B7. Adapt the ranges to suit.

 

[Max]

You're welcome.

Slight amendment to the range in:

=INDEX($D$1:$D$7,MATCH(TRUE,$C$1:$C$7=A1,0))

Above should have read as,
array-entered in B1, then copied down:
=INDEX($D$1:$D$8,MATCH(TRUE,$C$1:$C$8=A1,0))

(I missed row 8 of your range as posted earlier)