VLOOKUP - return cell is before lookup column

[Simon]

Can I VLOOKUP when the return value I require is in a
column before the lookup column.

A B
1 red
2 blue
3 green
4 yellow
5 white

Example:

I want to search for 'red' and return '1'.
I want to search for 'yellow' and return '4'.

The columns can not be moved.

Thank you for any help you can provide.

Simon

 

[Frank Kabel]

Hi
try
=INDEX(A1:A100,MATCH("red",B1:B100,0))

 

[A.W.J. Ales]

Simon,
Assuming your data range is A1:B5 and your lookupvalue is in E1 try :

=INDEX(A1:A5,MATCH(E1,B1:B5,0),1)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Simon]

Thank you