Vlookup

smokemalls

New Member
Joined
Sep 14, 2005
Messages
22
=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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why is your lookup value an entire column?
 

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
642
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
StuLux said:
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

New Member
Joined
Sep 14, 2005
Messages
22

ADVERTISEMENT

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

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
642
Office Version
  1. 365
Platform
  1. Windows
Quite right, my mistake, I seemed to recall using minus numbers in a vlookup function before but I stand corrected.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,462
Messages
5,572,260
Members
412,451
Latest member
newbie22922792
Top