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

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