Using VLOOKUP in an Excel Formula

Have you ever needed to look up current prices for an invoice for goods shipped? What about looking up the price discount for a loyal customer?

Excel can do these or any other data lookup using the VLOOKUP function. This function’s precise parameter requirements can seem intimidating at first, and a small error can produce unpredictable errors, but you’ll find it easy to use VLOOKUP if you follow the simple method below.

Set Up the Data

First enter your source data. This is the list from which Excel will look up some value. In this example, because Excel will need to look up a product name and find the price, the source is the price list. On each row, enter one product on each row, with the product name in the Column A and its price in Column B.

Be sure to sort the data in alphabetical order to ensure VLOOKUP can operate properly. If your data are not sorted, you can use the Sort button on the Data ribbon.

To reduce the chance of reference errors, give your data table a range name.

Create the Formula

To look up a product price, type “=VLOOKUP(A4,Sheet2!$a$2:$b$10,2,FALSE)”.

The parameters are:
• lookup_value: The value to find in the table. This value must appear in the first column of the source data. In this example, the lookup_value is the product name, which is in A4 on the invoice.

• table_array: The source data. In this example, the table_array is the price list that you created in the previous step. Be sure to use an absolute reference for the table if you plan to write this formula once and copy it to multiple cells. If you use a relative reference, your table_array reference will change, which lead to unpredictable (and incorrect) results.

• col_index_num: The column that holds the value you are looking for. In this example, because your formula is looking up the produce price, col_index_num is the number of the column that contains the prices. Note that it is a column number, not a letter. The column number is the position in your table.

• [range_lookup]: This is an optional parameter, as indicated by the square brackets, meaning that you may leave it blank. However, to be sure that the VLOOKUP formula functions as you expect, it’s a good practice to enter TRUE or FALSE. The only difference is when your VLOOKUP tries to find a product that does not exist in the price list.

o If TRUE, VLOOKUP will return the price for the last product that comes before the invalid product name in                              alphabetical order.

o If FALSE, VLOOKUP will return a #N/A error if the product does not exist in the price list.

Check the Results

Because VLOOKUP can be tricky to use, never assume it is returning the correct answer. Take a couple of minutes to look up some values yourself and verify that the results are correct.
In this example, the t-shirt price is showing $9.50. A quick glance back at the price list shows that $9.50 is, in fact, the correct price.

Next Steps

The VLOOKUP function has many other uses—looking up prior months’ results, replacing current prices with proposed prices to see the overall change, or grouping numeric data into layers. For any of these other applications, follow the same process: set up the data table, write the function, and test the results.
For more advanced applications, try exploring HLOOKUP, which working exactly like VLOOKUP except that it searches across the top row instead of down the left column. Or, for the most flexibility, try moving your data into a PivotTable, one of the most powerful tools available in Excel.