Note – Although this tutorial is based on Excel 2007, the majority of this tutorial will apply to earlier versions of Excel.
VLOOKUP stands for vertical lookup and it is a great way of finding related information in an Excel table. For the purposes of this tutorial we are going to use a simple price list as an example.
In this exercise we are going to find the cost of individual items using just a formula.
The example data
Set up a spreadsheet similar to the one shown below
Fig 1 - The sample data for the VLOOKUP tutorial
In cell E3 enter the following formula
=VLOOKUP(D3,A2:B7,2,FALSE)
In cell D3, type in the name of one of the items in column A and hit enter. Cell D3 will now contain the cost of that item.
Fig 2 - The results of our VLOOKUP formula
Formula Breakdown
The formula is constructed like this
VLOOKUP(Look up value, Range of data, Results column, Range lookup)
The look up value is the value we are searching by, in our example it will be the description of one of the items listed (Screwdriver, hammer etc). We have used cell D3 in our example to hold the item we are looking for.
The range of data is the range of cells we want to search and get the results from. In our case we are searching the range A2:A7 (the descriptions), but we want the corresponding results from the range B2:B7 (the prices). As a result we enter A2:B7 as the combined range of data.
Handy tip – If you intend to copy the formula in E3 to other cells and still have it search the range A2:B7 enter this as $A$2:$B$7. So the formula reads =VLOOKUP(D3,$A$2:$B$7,2,FALSE). If you don’t Excel will adjust this range automatically in relation to where you enter the formula.
The results column is the number of the column in our range of data we want our results to come from. The range A2:B7 is made up of 2 columns, and the results we want are in the second column, so we enter the value 2.
The range lookup tells Excel if we want an exact match or an approximate match when searching. This bit sounds back to front and trips a lot of people up, enter FALSE here for an exact match, and true for an approximate match. (I have no idea why Microsoft made it that way around). We want an exact match so we entered FALSE here.
-
-
-