# VLOOKUP

#### waynered

##### Board Regular
Can anybody explain VLOOKUP function and it's parameters.

Thanks

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### dk

##### MrExcel MVP
OK,

Consider this:-
Book3
ABCDEF
2
3This is your data - called Table Array.
4NameAgeEnter name >>>Dan<<< This is your Lookup Value
5Dan27
6Jon30Formula here >>>27<<< This formula looks up the persons
7Ben25age based on what's in cell E4.
8Paul22
9Mark31
...

The purpose of the VLOOKUP function in cell E6 is to return a person's age, based on their name (entered in cell E4). The VLOOKUP formula looks like this:-

=VLOOKUP(E4,A5:B9,2,FALSE)

The first argument (parameter) is the Lookup Value. This is the value we're going to search for in the data table.

The second argument (A5:B9) defines our data table - this is called Table Array by Excel. The values you're searching for must appear in the first column in this range.

The third argument tells Excel how many columns to count across in the range specified in the second argument. In this example column A is 1, column B is 2.

The last argument tells Excel whether to find an exact match (FALSE) or to find the closest match (TRUE). I personally always use FALSE but TRUE is quicker is your data is sorted and you want to find the closest match, if an exact match is not found.

So, in summary -

Take the lookup value in E4 (Dan), go and look in the Table Array. Search for Dan in the first column. If found, move to the second column. Return that value - 27.

I hope that's clear. I was trying to explain it to some of my colleagues recently and it can be a bit of a bugger to understand However, once you've got it, I think it's one of the most useful and powerful functions that Excel offers.

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-22 07:32

##### MrExcel MVP
& you can replace FALSE with 0, which you often see at this site.

Replies
1
Views
82
Replies
3
Views
302
Replies
5
Views
111
Replies
14
Views
282
Replies
4
Views
128

1,181,064
Messages
5,927,908
Members
436,575
Latest member
Tiger750

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back