How to lookup information in a worksheet using VLOOKUP

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.

An excel table for use with VLOOKUP

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).

  1. lookup value (this is the value you are looking up).  It is the cell number where you will be entering the lookup value
  2. table_array – This is the table or range of cells you want to lookup
  3. 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
  4. 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 values

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.

VLOOKUP formula explained

  1. 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
  2. B6 to E14 (highlighted in yellow in the table above) is the table_array, or the cell range where the lookup value is located.
  3. 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.
  4. FALSE is the range_lookup we want to use as we want an exact match returned
  5. 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.

  1. Sort the values in the CUSTOMERID column in ascending order
  2. 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
  3. Make sure you enter FALSE to get an exact match returned
  4. Test your formula by entering a different CUSTOMERIDs as lookup values in the cell B3
  5. 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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *