### Find Information in a Database with 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 information - 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

In the image shown above, VLOOKUP is used to find the unit price of an item based on its name. The name becomes the *lookup value* which VLOOKUP uses to find the price located in the second column.

### 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 you want to find in the first column of the *Table_array *argument.

**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 *Lookup_value* column as column 1;

- if *Col_index_num* is set to a number greater than the number of columns selected in the *Range_lookup* 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 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 TRUE or omitted and the first column of the range is not sorted in ascending order, an incorrect result might occur

- if set to FALSE, VLOOKUP only accepts an exact match for the *Lookup _value*.

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

### Exact vs. Approximate Matches

VLOOKUP can be set so that it returns only information that exactly matches the *Lookup _value *or it can be set to return approximate matches

The determining factor is the *Range_lookup *argument:

- set to FALSE it returns only information related to exact matches to the
*Lookup _value* - set to TRUE or omitted it returns exact or approximate information related to the
*Lookup _value*

In the example above, the *Range_lookup *is set to FALSE so VLOOKUP must find an exact match for the term *Widgets *in the data table order to return a unit price for that item. If an exact match is not found, a #N/A error is returned by the function.

**Note**: VLOOKUP is not case sensitive - both *Widgets* and *widgets* are acceptable spellings for the above example.

In the event that there are multiple matching values -* for example, Widgets is *listed more than once in column 1 of the data table - information related to the first matching value encountered going from top to bottom is returned by the function.

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

### VLOOKUP Function Example

In the image on the previous page, the following formula containing the VLOOKUP function is used to find the unit price for *Widgets* located in the table of data.

=VLOOKUP(A2,$A$5:$B$8,2,FALSE)

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 and eliminates the need to enter comma separators between arguments.

The steps below were used to enter the VLOOKUP function into cell B2 using the function's dialog box.

### Opening the VLOOKUP Dialog Box

- 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

The data that entered into the four blank rows of the dialog box form the arguments for the VLOOKUP function.

### 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, with point and click - which involves highlight the desired range of cells with the mouse pointer - can be used to enter them into the dialog box.

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

Relative cell references make it possible to search for multiple items in the same data table by copying VLOOKUP to multiple locations and entering different *lookup_values*.

### Entering the Function Arguments

- Click on the
*Lookup _value*line in the*VLOOKUP*dialog box - Click on cell A2 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 A5 to B8 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
*False*as the*Range_lookup*argument - Press the
*Enter*key on the keyboard to close the dialog box and return to the worksheet - The answer $14.76 - the unit price for a Widget - should appear in cell B2 of the worksheet
- When you click on cell B2, the complete function
*=VLOOKUP(A2,$A$5:$B$8,2,FALSE)*appears in the formula bar above the worksheet

### Excel VLOOKUP Error Messages

### 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! error is displayed if:

- The
*Col_index_num*argument is greater than the number of columns in table array.