The VLOOKUP function comes in very handy when you want to look up information in large worksheets that hold long lists of items such as customer information, product information, shipment details, etc.
The VLOOKUP function essentially finds a value in the leftmost column of a range of cells, such as a table, and then returns the found value to the cell where you placed the VLOOKUP formula. At first glance, the VLOOKUP function can be very intimidating and just looking at it can scare people off from using it. However, once you understand how it works, it’s actually quite easy to use.
Using VLOOKUP
Keep in mind that before you can use the VLOOKUP function, you need to identify a primary key column that contains a unique value for each row in your worksheet. An example of a primary key would be a customer ID. The primary key is usually the first column in your table of data.
Next, decide what values you want to lookup. In the example below, we want to lookup the Cost of Goods for a customer by entering a unique CustomerID. Now, set up two cells in your spreadsheet as shown below, one to enter your search value (CustomerID) and the other to contain the result found (Cost of Goods).
For demonstration purposes, we are using the following spreadsheet that contains the CustomerID field as our primary key field.
The VLOOKUP Formula
Now let’s look at the VLOOKUP formula itself. VLOOKUP requires 4 pieces of information:
VLOOKUP( lookup_value, table_array, col_index_number, range_lookup).
- lookup value (this is the value you are looking up). It is the cell number where you will be entering the lookup value
- table_array – This is the table or range of cells you want to lookup
- col_index_number (this is the column which contains the search result) You have to count the number of columns from the left beginning with the first column in your table or range of cells and that will be the column index number
- range_lookup. This can be TRUE or FALSE. If you want an exact match returned, the argument should be set to FALSE. If you want an approximate value, the argument should be set to TRUE.
VLOOKUP Example
In our example of VLOOKUP, we want to look up the Cost of Goods in our worksheet by entering a unique CUSTOMERID.
If the =VLOOKUP(B3,B6:E14,4,FALSE) formula is used, when you enter CUS109 in cell B3 and press ENTER, the VLOOKUP function will search the first column of the table until it finds an exact match, and then it returns the value $155.36.
VLOOKUP formula Explained
The VLOOKUP formula is explained below. Keep in mind that you have to enter the VLOOKUP formula in the cell where you want the result displayed. In our example, it is cell C3 as shown below.
- B3 is the lookup_value which is the cell number where you will be entering your unique lookup value. In our example, we will be entering a unique CUSTOMERID in cell B3
- B6 to E14 (highlighted in yellow in the table above) is the table_array, or the cell range where the lookup value is located.
- 4 is col_index_num, or the column number in table_array that contains the return value. In our example, the fourth column in the table array is where the Cost of Goods value we are looking for resides so the formula output will be a value returned from the Cost of Goods column.
- FALSE is the range_lookup we want to use as we want an exact match returned
- Output of the VLOOKUP formula is 155.36, the Cost of Goods for CUS109, the CUSTOMERID we entered in cell B3.
Practice: – Look up information in a worksheet
Open the CustLookupTable spreadsheet provided for you here or create your own with the data demonstrated above. Note: This spreadsheet was created with Excel 2016.
- Sort the values in the CUSTOMERID column in ascending order
- In the cell C3, the cell where you want the result to appear, enter the VLOOKUP formula explained above. This formula contains the unique arguments you need to display the result
- Make sure you enter FALSE to get an exact match returned
- Test your formula by entering a different CUSTOMERIDs as lookup values in the cell B3
- Edit the formula so that it finds the CustomerName value for different CUSTOMERIDs
Other useful resources to help you learn
Quick Reference Card – This is a handy VLOOKUP two-page reference card that explains what the arguments mean and how to use them from Microsoft. The VLOOKUP reference card opens as a PDF file in Adobe Reader that you can download to your computer.
Troubleshooting Tips from Microsoft – This is a three-page reference card that offers basic troubleshooting tips for VLOOKUP.