VLOOKUP

Using the Excel VLOOKUP Function

Summary

The VLOOKUP function is one of Excel's lookup and reference functions and is used to find things in a table or range by row. For example, VLOOKUP could be used to find the price of a widget by part number.

If you described the various arguments of the VLOOKUP function verbally, you would have the following:

=VLOOKUP(value you are looking for, range where you will lookup the value, the column number in the range containing the value, do you want an exact or approximate match). The exact or approximate match is indicated with a 1 (TRUE) or 0 (FALSE).


Syntax

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

Syntax Breakdown

Lookup Value
Required. The value you want to look up. This value must be in the first column of the range of cells that you select in table_array.

Table Array
Required. The range of cells where the function will search for the lookup_value and return the value.

Col Index Num
Required. The column number that contains the return value. The left-most column of table_array is considered column 1.

Range Lookup
Optional. A logical value indicating if you want an exact or approximate match.

Enter 1, or TRUE, if you would like an approximate match. This is the default setting. The function will match the nearest value in the table that is less than value. Even if range_lookup is set to TRUE, the function will return an exact match if one exists.

Enter 0, or FALSE, if you want an exact match. If you are using FALSE, the values in the first column of the table do not need to be sorted.


Usage Notes

The VLOOKUP function is used to find and retrieve data from a specific column in a table. Lookup values are always contained in the first column of the table with lookup columns to the right.

The "V" in VLOOKUP stands for vertical. This function is used when your data is organized into vertical rows. If your data is organized horizontally, use the HLOOKUP function.

VLOOKUP Only Looks Right
The VLOOKUP function requires that the lookup values are contained in the left-most column. The data that you wish to retrieve can be contained in any column to the right. The function retrieves data based on the column number. To better understand this concept, imagine that every column is numbered. The left-most column is #1, followed by #2, #3, etc. Supplying the appropriate column number to col_index_num will return a value from that column.

Excel VLOOKUP Column Numbering

Matching Modes
VLOOKUP can match either an exact or approximate value. This is controlled by the range_lookup argument. Setting the argument to FALSE will force an exact match, whereas TRUE is for approximate matching. TRUE is the default setting if the argument is omitted.

If you have a unique ID as a lookup_value, using FALSE for an exact match will be the best course of action:

Excel VLOOKUP Exact Match

If an exact match isn't necessary, or may not be possible, use TRUE for an approximation. In this scenario you are asking VLOOKUP to return the best match for a given value, but it doesn't have to be exact. If you supply a value that is larger than the values in table_array, the function will drop back to the next largest value.

It is important to sort your data in ascending order by lookup_value when using an approximate match.

Excel VLOOKUP Approximate Match

Common Errors
The function returns the #N/A error if:
  • Range_lookup is set to TRUE and the value in lookup_value is smaller than the smallest value in the first column of table_array
  • Range_lookup is set to FALSE and an exact match cannot be found

VLOOKUP returns the #REF! error if the col_index_num argument is greater than the number of columns in table_array. For example, if you specify column 5 when table_array only has 4 columns you will receive the #REF! error.

If table_array is less than 1 the function returns the #VALUE! error.

If you are looking up name, be sure to use quotes. The function returns the #NAME? error when quotes are missing. For example, to look up the name "Bob" use =VLOOKUP("Bob",A1:B5,2,FALSE).

Using Wildcards
VLOOKUP supports wildcard characters - question mark (?) and asterisk (*) - in the lookup_value argument. Use a question mark to match any single character and the asterisk to match any sequence of characters. If you must find an actual question mark or asterisk, enter the tilde (~) in front of the character.

Excel VLOOKUP Wildcards