VLOOKUP
Finds a value in the first column of a table and returns data from the same row: a price by product ID, a department by employee number.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- [ ]optional part
- lookup_value
- What to look for: the cell with the key, for example a product ID in A2.
- table_array
- The lookup table without headers. The key must be in its first column.
- col_index_num
- Which column of the table to return the value from. The first column of the table is 1.
- range_lookup
- 0 means exact match. 1 means the nearest smaller value, only for scales sorted in ascending order.Optional
Return value
The value from the matching row, taken from the column with the given number. If the key isn't found, you get #N/A; if the column number is wider than the table, you get #REF!.
Examples
Price by product ID
Details
VLOOKUP looks for a key in the leftmost column of a lookup table and returns the value from the column with the number you give. The number is counted from the start of the table, not from the start of the sheet.
The last argument is almost always 0: that makes VLOOKUP look for an exact match. If the key isn't there, the function returns #N/A.
Lock the lookup table with $ signs so it doesn't shift when you fill the formula down: $E$2:$G$6.
Common mistakes
- The key isn't in the first column of the table. VLOOKUP can't look to the left: use INDEX with MATCH or XLOOKUP.
- The key has an extra space, or a number is stored as text. The values look the same, but VLOOKUP returns #N/A.
- Without $ signs the table shifts when you fill down, and the lower rows get #N/A.
- The last argument is left out. Then VLOOKUP does an approximate match and silently returns the wrong value on unsorted data.