How to Find Data with VLOOKUP in Excel

01
of 03

Find Approximate Matches to Data with Excel's VLOOKUP

Find Data in Excel with VLOOKUP
Find Price Discounts with VLOOKUP. © Ted French

How the VLOOKUP Function Works

Excel's VLOOKUP function, which stands for vertical lookup, can be used to look up specific information located in a table of data or database.

VLOOKUP normally returns a single field of data as its output. How it does this is:

  1. You provide a name or lookup_value that tells VLOOKUP in which row or record of the data table to look for the desired data
  2. You supply the column number - known as the col_index_num - of the data you seek
  3. The function looks for the lookup_value in the first column of the data table
  4. VLOOKUP then locates and returns the information you seek from another field of the same record using the supplied column number

Sorting the Data First

Although not always required, it is a usually best to first sort the range of data that VLOOKUP is searching in ascending order using the first column of the range for the sort key.

If the data is not sorted, VLOOKUP might return an incorrect result.

The VLOOKUP Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the VLOOKUP function is:

= VLOOKUP (lookup_value , table_array , col_index_num , range_lookup)

lookup _value - (required) the value to search for - such as the quantity sold in the image above

table_array - (required) this is the table of data that VLOOKUP searches to find the information you are after.

  • The table_array must contain at least two columns of data
  • The first column normally contains the lookup_value

col_index_num - (required) the column number of the value you want found.

  • The numbering begins with the search_key column as column 1
  • If col_index_num is set to a number greater than the number of columns selected in the table_array argument a #REF! error is returned by the function

range_lookup - (optional) indicates whether or not the range is sorted in ascending order.

  • The data in the first column is used as the sort key
  • A Boolean value - TRUE or FALSE are the only acceptable values
  • If omitted, the value is set to TRUE by default
  • If set to TRUE or omitted and the first column of the range is not sorted in ascending order, an incorrect result might occur
  • If set to TRUE or omitted and an exact match for the lookup _value is not found, the nearest match that is smaller in size or value is used as the search_key
  • If set to FALSE, VLOOKUP only accepts an exact match for the lookup _value. If there are multiple matching values, the first matching value is returned
  • If set to FALSE and no matching value for the search_key is found, a #N/A error is returned by the function

Example: Find the Discount Rate for Quantity Purchased

The example in the image above uses the VLOOKUP function to find the discount rate that varies depending upon the quantity of items purchased.

The example shows that the discount for the purchase of 19 items is 2%. This is because the Quantity column contains ranges of values. As a result, VLOOKUP cannot find an exact match. Instead, an approximate match must be found in order to return the correct discount rate.

To find approximate matches:

  • sort the data in the table_array in ascending order;
  • set the range_lookup argument to TRUE

In the example, the following formula containing the VLOOKUP function is used to find the discount for quantities of goods purchased.

=VLOOKUP(C2,$C$5:$D$8,2,TRUE)

Even though this formula can just be typed into a worksheet cell, another option, as used with the steps listed below, is to use the function's dialog box to enter its arguments.

  • Using the dialog box often makes it easier to enter a function's arguments correctly.

Opening the VLOOKUP Dialog Box

The steps used to enter the VLOOKUP function shown in the image above into cell B2 are:

  1. Click on cell B2 to make it the active cell - the location where the results of the VLOOKUP function are displayed
  2. Click on the Formulas tab.
  3. Choose Lookup & Reference from the ribbon to open the function drop down list
  4. Click on VLOOKUP in the list to bring up the function's dialog box
02
of 03

Entering Excel's VLOOKUP Function's Arguments

Entering Arguments into the VLOOKUP Dialog Box
Entering Arguments into the VLOOKUP Dialog Box. © Ted French

Pointing to Cell References

The arguments for the VLOOKUP function are entered into separate lines of the dialog box as shown in the image above.

The cell references to be used as arguments can be typed into the correct line, or, as done in the steps below, pointing, which involves highlight the desired range of cells with the mouse pointer, can be used to enter them into the dialog box.

The advantages of using pointing include:

  • It's faster than typing;
  • Fewer mistakes are made entering the correct cell references.

Using Relative and Absolute Cell References with Arguments

It is not uncommon to use multiple copies of VLOOKUP to return different information from the same table of data. To make it easier to do this, often VLOOKUP can be copied from one cell to another. When functions are copied to other cells, care must be taken to ensure that the resulting cell references are correct given the function's new location.

In the image above, dollar signs ( $ ) surround the cell references for the table_array argument indicating that they are absolute cell references, which means they will not change if the function is copied to another cell. This is desirable as multiple copies of VLOOKUP would all reference the same table of data as the source of information.

The cell reference used for lookup_value, on the other hand, is not surrounded by dollar signs, which makes it a relative cell reference. Relative cell references change when they are copied to reflect their new location relative to the position of the data they refer to.

Entering the Function Arguments

  1. Click on the Lookup _value line in the VLOOKUP dialog box
  2. Click on cell C2 in the worksheet to enter this cell reference as the search_key argument
  3. Click on the Table_array line of the dialog box
  4. Highlight cells C5 to D8 in the worksheet to enter this range as the Table_array argument - the table headings are not included
  5. Press the F4 key on the keyboard to change the range to absolute cell references
  6. Click on the Col_index_num line of the dialog box
  7. Type a 2 on this line as the Col_index_num argument, since the discount rates are located in column 2 of the Table_array argument
  8. Click on the Range_lookup line of the dialog box
  9. Type the word True as the Range_lookup argument
  10. Press the Enter key on the keyboard to close the dialog box and return to the worksheet
  11. The answer 2% (the discount rate for the quantity purchased) should appear in cell D2 of the worksheet
  12. When you click on cell D2, the complete function  =VLOOKUP(C2,$C$5:$D$8,2,TRUE) appears in the formula bar above the worksheet

Why VLOOKUP Returned 2% as a Result

  • In the example, the Quantity column does not contain an exact match for the search_key value of 19.
  • Since the is_sorted argument is set to TRUE, VLOOKUP will find an approximate match to the search_key value.
  • The nearest value in size that is still smaller than the search_key value of 19 is 11.
  • VLOOKUP, therefore, looks for the discount percent in the row containing 11, and, as a result, returns a discount rate of 2%.
03
of 03

Excel VLOOKUP Not Working: #N/A and #REF Errors

The Excel VLOOKUP Function Returns the #REF! Error Message
VLOOKUP Returns the #REF! Error Message. © Ted French

VLOOKUP Error Messages

The following error messages are associated with VLOOKUP.

A #N/A ("value not available") Error is Displayed If:

  • The lookup _value is not found in the first column of the range argument
  • The Table_array argument is inaccurate. For example, the argument may include empty columns on the left side of the range
  • The Range_lookup argument is set to FALSE and an exact match for the search_key argument cannot be found in the first column of the range
  • The Range_lookup argument is set to TRUE and all of the values in the first column of the range are larger than the search_key

A #REF! ("reference out of range") Error is Displayed If:

  • The Col_index_num argument is greater than the number of columns in the Table_array