lookup

[choice]

if i have an range (a1:g1000) will a lookup function be able to search for a value in the range and show a result to the left of the input

=vlookup(m1,$a$1:$G$1000,-2)

john 5 blu
jane 7 re
dog 3 gree
cat 1 blac

example if i type in black for the lookup, could it result "1" or "cat"?

 

[Jason Morin]

No...you'd have to use a combination of INDEX and MATCH.

=INDEX(A1:C1000,MATCH(M1,C1:C1000,0),1)

will return the corresponding value in column A. For
column B, use a "2" in place of the "1".

HTH
Jason
Atlanta, GA

-----Original Message-----
if i have an range (a1:g1000) will a lookup function be

able to search for a value in the range and show a result
to the left of the input.

 

[Manish]

No, the column which you want to use for lookup i.e. in
your example,colors should be in Col. A instead of Col.C.

and the formula to get cat out of black will be :

=vlookup(m1,$a$1:$c$1000,2) where m1 = Black, Col. A has
colors, Col. B has dog, cat, john, etc. and Col. C has
5,7,3,etc.

If you want to get '1' instead of 'cat', simply change the
above function as under :
=vlookup(m1,$a$1:$c$1000,3).

Thanks,

Manish

-----Original Message-----
if i have an range (a1:g1000) will a lookup function be

able to search for a value in the range and show a result
to the left of the input.

 

[Gord Dibben]

See Chip Pearson's help on Left Lookups.

http://www.cpearson.com/excel/lookups.htm#LeftLookup

Gord Dibben Excel MVP