ExcelFunctions.net Logo

The Excel VLOOKUP Function

Home » Excel-Built-In-Functions » Excel-Lookup-And-Reference-Functions » Excel-Vlookup-Function

Search this site:
Custom Search

Basic Description
Related Functions :

The Excel VLOOKUP function 'looks up' a given value in the left-hand column of a data array (or table), and returns the corresponding value from another column of the array.

The format of the function is:

VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

where the arguments are as follows:

lookup_value - The value that you want to look for, in the left-hand column of the supplied data array
table_array - The data array or table, that you want to search the left hand column of, for the supplied lookup_value
col_index_num - The column number, within the supplied array, that you want the corresponding value to be returned from
[range_lookup] -

An optional logical argument, which can be set to TRUE or FALSE, meaning :

TRUE - if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value
(Note: If range_lookup is set to TRUE, the left-hand column of the table_array must be in ascending order)
FALSE - if the function cannot find an exact match to the supplied lookup_value, it should return an error


Examples

Vlookup Example 1

In the spreadsheet below, columns A and B list an inventory of grocery items, and their prices, and cell E2 of the spreadsheet shows a simple example of the Vlookup function being used to look up the price of an item from the inventory.

Example of use of the Excel Vlookup Function

The above Vlookup function returns the price for "Cornflakes", which is $3.50.

In this example:

- the lookup_value is the text string "Cornflakes", which is located in cell D2
- the table_array is columns A-B of the spreadsheet
- the col_index_num is set to 2, to denote that the value returned should be taken from column 2 of the table_array
- the range_lookup argument is set to FALSE, to indicate that we only want a result to be returned if an exact match to the lookup_value is found


Vlookup Example 2

In the spreadsheet below, columns A-C list the grades that are assigned to examination marks lying within the ranges 0-44%, 45%-54%, etc.

Cell F2 shows the score of 52% that was achieved by the student "Anne" in an examination. The Vlookup function in cell G2 looks up this score in column A of the spreadsheet and returns the associated grade from column C. Note that, in this example, if the exact score of 52% is not found in column A, we want, instead, to use the nearest value below this score.

Example of use of the Excel Vlookup Function

The above Vlookup function returns the grade for the score 52%, which is E.

In this example:

- the lookup_value is the value 52%, which is located in cell F2
- the table_array is the range A2-C7 of the spreadsheet
- the col_index_num is set to 3, to denote that the value returned should be taken from column 3 of the table_array
- the range_lookup argument is set to TRUE, to indicate that, if an exact match to the lookup_value is not found, we want to use the closest value below the lookup_value


Further Vlookup Examples

For a practical example of the VLOOKUP function being used to create a variable chart, see the Excel Variable Chart page.

Also, there is an excellent video tutorial on the Excel Vlookup function on the Brainstorm Inc. website, and several further examples on the Microsoft Office website.


Trouble Shooting

If you get an error from the Excel Vlookup function this is likely to be one of the following :

Common Errors
#N/A -
Occurs if the Vlookup function fails to find a match to the supplied lookup_value
The cause of this will generally depend on the supplied range_lookup:
if range_lookup = TRUE
(or is omitted)		 - the #N/A error is likely to be because the smallest value in the lookup column is greater than the supplied lookup_value
if range_lookup = FALSE -

the #N/A error is likely to be because an exact match to the lookup_value is not found in the lookup column

if you can't understand why this is, check out Common Problem No. 1 (below).
#VALUE! - Occurs if either:
- The supplied col_index_num argument is < 1 or is not recognised as a numeric value
or
- The supplied range_lookup argument is not recognised as TRUE or FALSE
#REF! - Occurs if the supplied col_index_num argument is greater than the number of columns in the supplied table_array


Also, the following problems are encountered by some users:

You can see the value that you want to look up in your table_array, but your Excel VLOOKUP is returning the #N/A error. Why can't it 'see' the lookup_value in the table_array?

Possible Reason No. 1

You may have unseen spaces at the start or end of either the value you are looking up, or in the cells of your table_array. These spaces cause your lookup_value cell and the 'matching' cell in your table_array to have slightly different content.

Solution No. 1
Check the contents of the cells that you believe should match. If there are unseen additional spaces in one or both of the lists, remove these using the TRIM function:
  1. Create a new column next to the one with the additional spaces in it
  2. Use the TRIM function in your new column, to get rid of extra spaces
  3. Copy the contents of the new column and paste these over the top of the original column, using paste->special->values
  4. Delete the column containing the TRIM function
Possible Reason No. 2
The contents of the cells that are being compared may have different data types. For example, the cell containing your lookup_value may be stored as a number by Excel, whereas the values in your table_array may be stored as text even though they look like numbers.

Solution No. 2
Force both sets of data to have the same type. For example, if you want both sets of values to be stored as text, convert both sets of data to text, using Excel's Text To Columns tool:
  1. Use the mouse to select the cells you want to convert to text (this must not span more than one column)
  2. From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
  3. Make sure the Delimited option is selected and click next
  4. Make sure all the delimiter options are unselected and then click next again
  5. You should now be offered a selection of Column Data Formats. Select Text and click the Finish button

The data in your selected cells should now be stored as text within Excel and so the Excel Vlookup function should be able to 'look up' the matching value.

Note that you could have chosen to convert the contents of your cells to Excel's 'general' type, by simply selecting the column data format General in the Text To Columns tool.
Common Vlookup Problem No. 2

You use the Excel Vlookup function in one cell and it works perfectly. However, when you attempt to copy the function down to other rows, you get the #REF error.

Possible Reason

This error will occur if you have referenced the range 1:65536 (a reference to all cells on a worksheet) in your initial cell. Excel automatically increases row references when a cell is copied down to other rows. However, row 65536 is the last row in the spreadsheet, so rows 65537, 65538, ... don't exist.

Quick Solution

If your initial cell (in which the function works) references the cell range 1:65536, change this reference to $1:$65536

Note: the $ signs prevent Excel from increasing the row reference as the formula is copied down to other cells.




Valid XHTML 1.0 Transitional

Disclaimer Privacy Policy
Copyright © 2008-2010 ExcelFunctions.net