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.
=NsLookup('www.juniper.net',1) Performs a lookup on the TargetURL host “www.juniper.net” and returns the time taken in milliseconds to receive the results from the name server =NsLookup('CheckTheNetwork.com',2,”66.80.130.23”).
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
Find Information in a Database with VLOOKUP
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.
= 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
In the first example image above, the following formula containing the VLOOKUP function is used to find the unit price for Widgets located in the table of data.
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, shown above, 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
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
- The Col_index_num argument is greater than the number of columns in table array.