### Find Approximate Matches to Data with Excel's VLOOKUP

### 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:

- 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 - You supply the column number - known as the
*col_index_num*- of the data you seek - The function looks for the
*lookup_value*in the first column of the data table - 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:

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

### Entering Excel's VLOOKUP Function's Arguments

### 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

- Click on the Lookup _value line in the
*VLOOKUP*dialog box - Click on cell C2 in the worksheet to enter this cell reference as the
*search_key*argument - Click on the
*Table_array*line of the dialog box - Highlight cells C5 to D8 in the worksheet to enter this range as the
*Table_array*argument - the table headings are not included - Press the
*F4*key on the keyboard to change the range to absolute cell references - Click on the
*Col_index_num*line of the dialog box - 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 - Click on the
*Range_lookup*line of the dialog box - Type the word
*True*as the*Range_lookup*argument - Press the
*Enter*key on the keyboard to close the dialog box and return to the worksheet - The answer 2% (the discount rate for the quantity purchased) should appear in cell D2 of the worksheet
- 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%.

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

### 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*