Lookup and Reference - VLOOKUP Function

Description

Use VLOOKUP when you need to find things in a table or a range by row. Organize your data so that the value you look up is to the left of the return value you want to find.

Syntax

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

Arguments

Argument	Description	Required/ Optional
lookup_value	The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in table_array. Lookup_value can be a value or a reference to a cell.	Required
table_array	The range of cells in which the VLOOKUP will search for the lookup_value and the return value. The first column in the cell range must contain the lookup_value. The cell range also needs to include the return value you want to find.	Required
col_index_num	The column number (starting with 1 for the left-most column of table-array) that contains the return value.	Required
range_lookup	A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match − TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you do not specify one. FALSE searches for the exact value in the first column.	Optional

Notes

Use absolute references for range_lookup as it allows you to fill-down a formula so that it always looks at the same exact lookup range.
When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. Otherwise, VLOOKUP might return an incorrect or unexpected value.
If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters—the question mark (?) and the asterisk (*)—in lookup_value. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the character.
If range_lookup is TRUE or left out, the first column of the table_array needs to be sorted alphabetically or numerically. If the first column is not sorted, the return value can be wrong.
Either sort the first column of the table_array, or use FALSE for an exact match.
When searching text values in the first column, make sure the data in the first column does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP might return an unexpected value
If range_lookup is TRUE, then if the value in the lookup_value is smaller than the smallest value in the first column of the table_array, you will get the #N/A error value
If range_lookup is FALSE and the exact number isn't found, you will get the #N/A error value
If col_index_num is greater than the number of columns in table_array, you will get the #REF! error value
If the table_array is less than 1, you will get the #VALUE! error value
If the formula is missing quotes, you will get the #NAME? error value

Applicability

Excel 2007, Excel 2010, Excel 2013, Excel 2016

Example

advanced_excel_lookup_reference_functions.htm