Vlookups are notoriously difficult but incredibly useful for many applications (possible uses detailed below). I normally spend around 45 minutes teaching this difficult topic, hopefully this quick blog will aim to introduce the topic briefly!
With many built-in functions, Excel is more than just a spreadsheet. One of the most useful of these database functions is VLOOKUP, which looks up a value stored in a lookup table. You can use VLOOKUP to locate the price of an item, a tax value, postal code or other frequently used (or difficult-to-remember) information.
The lookup value resides in the left-most column of the lookup table. The lookup table can have many columns, but the value that triggers the lookup must always be stored in the first column in the table.
The basic formula for the VLOOKUP function is:
=VLOOKUP(lookup_value,table_array, col_index_num, range_lookup)
The cell containing the value to be looked up is the lookup_value. VLOOKUP compares the value of this cell to the contents of the lookup table.
The table_array is the range of rows and columns covered by the lookup table.
col_index_num is the column number where the values being looked up can be found
range_lookup is an optional argument. When left blank, VLOOKUP scans the first column in the table for the value that most closely matches the lookup request. If you need to have an exact match, assign a value of FALSE to the range_lookup.
Use the following example to make a simple lookup table. This example will look up the price of five different products. The first column will hold the name of the item. The second column will hold its price.
1. First, open Excel.
2. Enter the following data in an array using any cell as a starting point. Your array will have five rows and two columns. Do not include the commas in the data you enter.
(Column 1, Column 2)
Magazine, £1
Book, £2
Calendar, £3
CD, £4
DVD, £5
3. Once the lookup table is created, select another cell somewhere on the worksheet and type one of the values from Column 1 of your lookup table.
4. Click on the cell immediately to the right of the value you just entered.
5.From the Insert menu, select "Function" and select VLOOKUP. You can find it under Lookup and Reference.
6. Enter the values in the formula wizard by clicking on the cell you want. The lookup_value will be the cell you entered data into in Step 3.
7. The table_array is the group of cells where your lookup table can be found. To enter this array into the wizard, click on the first element in Column 1 and drag to the last element in column 2.
8. The col_index_num is the number of the column in which the looked up value is found. In this example, the column is the second (price) column. Enter a 2 in this field.
9. The range_lookup value can be TRUE or FALSE. If TRUE, the lookup table must be sorted in ascending order based on the values in Column 1. In this state, the function will accept only exact matches. If FALSE, the lookup table does not need to be sorted and will return the value in the lookup table that most closely matches what you've entered.
10. Once the function is complete, click OK
The function should now return a value from the lookup table. To verify the lookup is working, look up the price of a different item on the list by entering a different list value in the cell you created in Step 3.
Lookup tables have many applications but they work best in situations where values must be referred to frequently, or where centrally stored data are used by a number of people.