VLOOKUP

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

Forum statistics

Threads
1,144,290
Messages
5,723,522
Members
422,502
Latest member
barakgahtan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top