Excel VLOOKUP function
What is VLOOKUP? To begin with, it is an Excel function :) What does it do? It searches for the value you specify and returns a matching value from another column. More technically, the VLOOKUP function looks up a value in the first column of a given range and returns a value in the same row from another column.
In its common usage, Excel VLOOKUP searches through your data set based on the unique identifier and brings you a piece of information associated with that unique identifier.
The letter "V" stands for "vertical" and is used to differentiate VLOOKUP from the HLOOKUP function that looks up a value in a row rather than column (H stands for "horizontal").
The function is available in all versions of Excel 365 through Excel 2007.
Tip. In Excel 365 and Excel 2021, you can use the XLOOKUP function, which is a more flexible and powerful successor of VLOOKUP.
VLOOKUP syntax
The syntax for the VLOOKUP function is as follows:
Where:
- Lookup_value (required) - is the value to search for.
This can be a value (number, date or text), cell reference (reference to a cell containing a lookup value), or the value returned by some other function. Unlike numbers and cell references, text values should always be in enclosed in "double quotes". - Table_array (required) - is the range of cells where to search for the lookup value and from which to retrieve a match. The VLOOKUP function always searches in the first column of the table array, which may contain various text values, numbers, dates, and logical values.
- Col_index_num (required) - is the number of the column from which to return a value. The counting starts from the leftmost column in the table array, which is 1.
- Range_lookup (optional) - determines whether to search for approximate or exact match:
- TRUE or omitted (default) - approximate match. If an exact match is not found, the formula searches for the largest value that is smaller than the lookup value. Requires sorting the lookup column in ascending order.
- FALSE - exact match. The formula searches for a value exactly equal to the lookup value. If an exact match is not found, a #N/A value is returned.