Vlookup

[smokemalls]

=VLOOKUP(A:A,$A$1:$B$4,2)

The above looks up value in column A in range A1:B2 and returns value in column 2. Is it possible to look up value in collumn B in range A1:B4 and return value in column 1 eg;

=VLOOKUP(B:B,$A$1:$B$4,1)

This does not work though. Is there a way around this please???

Cheers

 

[Scott Huish]

Why is your lookup value an entire column?

 

[StuLux]

VLOOKUP always looks up a value in a table with the lookup column being column 1 so if you wnat a value from a column to the left of the look up column use a minus number eg. to look up a value to the left of the look up column enter -1 etc.

 

[NBVC]

VLOOKUP always looks up a value in a table with the lookup column being column 1 so if you wnat a value from a column to the left of the look up column use a minus number eg. to look up a value to the left of the look up column enter -1 etc.

With Vlookup, the lookup value has to be within the left-most column in the range, I don't think -1 works.

You need the Index/Match combination:

Something like:
=INDEX($A$1:$B$4,MATCH(B1,$B$1:$B$4,0),1)

 

[smokemalls]

Sorry can you explain that futher?? i dont understand what it is used for???

 

[StuLux]

Quite right, my mistake, I seemed to recall using minus numbers in a vlookup function before but I stand corrected.

 

[smokemalls]

done, cheers