# Vlookup

#### smokemalls

##### New Member
=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

### 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
Why is your lookup value an entire column?

#### StuLux

##### Well-known Member
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
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

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

#### StuLux

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

done, cheers

Replies
8
Views
150
Replies
3
Views
123
Replies
1
Views
110
Replies
12
Views
154
Replies
6
Views
111