applied tips : november 2008
microsoft excel : VLOOKUP explained
Find data in one worksheet and return it to another using VLOOKUP and HLOOKUP
This pair of functions can make your spreadsheet feel more like a database. They allow Excel to return a number based on a value inside a table that you define. This is commonly used to look up prices, but can be for just about anything.
Setting up the Example
Let's pretend we have the following two worksheets, Invoice and Pricing:
On our Invoice worksheet, in cells E4:E7, we want the price of the item to appear based on what is in B4:B7. If the user types "Apples" we want $0.95 to appear. If they change it to Bananas, we want $1.79 to automatically appear.
Creating the Lookup Table
Our second worksheet, Pricing, must be set similar to how you see it here. There is a list of product names in the first column, and a list of corresponding prices in the second column.
The Pricing worksheet can appear anywhere in the workbook, and it can be called anything you like. It doesn't have to start at A1, but that makes it easier to manage.
Finally, we're going to give this lookup table a Name. This will make it easier to refer to in our formulas. In this example we're going to select all of the cells from A2:B7 (skipping Row 1).
In Office 2003, press Control + F3 to open the Define Name dialog box. Enter a simple name, such as Products, that doesn't contain spaces or any non-alphanumeric characters. Click Add and press OK.
In Office 2007, press Control + F3 to open the Name Manager dialog box. Click New, then enter a simple name, such as Products, that doesn't contain spaces or any non-alphanumeric characters. Ignore Scope and Comment and click OK, then Close.
We've now given the name "Products" to that range of cells which we can refer to in our VLOOKUP formula.
Writing the Formula
Now we're going to create the formula that goes into cell E4 of our Invoice worksheet. It will look like this:
= VLOOKUP(B4,Products,2, False)
Let's break down the four arguments in the VLOOKUP function:
- This is the cell that we're going to find in the first column of our lookup table (Products). In the case of B4, we're looking for the word "Apples". It needs to exist in the Products table or an error will be returned.
- This is the Name (or address) of our lookup table. We called it Products, but we could have also referred to it using standard Excel address notation, such as Products!A2:B7.
- This is the column number from our lookup table that we want returned. Our lookup table has the price in the second column, hence the 2 here. Suppose we had additional columns in the lookup table such as weight, item code, or tax. We could return those columns just as well by specifying 3, 4, or 5 respectively.
- By indicating False here, we require Excel to find an exact match from the lookup table or return an error.
Now that we're done with the formula, when the value in B4 changes to another product, the price automatically changes as long as it exists in our Products table.
All we have to do now is take our VLOOKUP formula in E4 and fill it down through E7. Presto!
There's two more steps we could take to really make this invoice bullet-proof. One is to make the list of products in Column B of our invoice be a pull-down menu so that the user cannot specify a product doesn't exist. Another is to lock-down the entire worksheet so that the formulas and formatting cannot be edited. We'll cover that next month.
schedule a class on excel
Request an on-site Excel class from Applied Office. Sessions are inexpensive and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time. Learn more here