excel VLOOKUP
Introduction
Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.
The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.
Syntax
- VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Parameters
| Parameter | Description |
|---|---|
| lookup_value | The value you're searching for in the left column of the table. Can be either a fixed value, cell reference or named range (required) |
| table_array | The range of cells consisting of the column you want to search in on the left side with values in cells to the right that you want returned. Can be an Excel cell reference or a named range. (required) |
| col_index_num | The number of the column you want to return data from, counting from the left-most column in your table (required) |
| range_lookup | Controls the way the search works. If FALSE or 0, Excel will perform an exact search and return only where there is an exact match in the left most column. Attention to precision with rounding is very important for this search with numeric values. If TRUE or 1, Excel will perform and approximate search and return the last value it equals or exceeds. As such the first column must be sorted in ascending order for and an approximate search. range_lookup. (Optional - defaults to TRUE) |
Remarks
Similar functions:
- HLOOKUP (the same as VLOOKUP but searches horizontally rather than vertically)
- MATCH (if your lookup_value has a match, returns the row number within the range)
- LOOKUP (similar to VLOOKUP and MATCH, and is provided for backward compatibility)
Common errors:
- Not setting the range_lookup parameter and getting the default, non-exact match behaviour
- Not fixing and absolute address range in the table_array - when copying a formula, the "lookup table" reference also moves
