The VLOOKUP function is the most used lookup function in the lookup function collection. Understanding its structure and logic are paramount when performing lookup operations.
Explaining VLOOKUP in English
The VLOOKUP function takes a value you know, like a part number, and locates the value in a list. Once the known value is located in the list, an associated piece of information is returned from that record, such as the color, weight, or price of the part number.
The syntax for the VLOOKUP function is as follows:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
- Lookup_value – is the know information, or the value you are trying to find in the list.
- Table_array – is the list of items with related information. The first column in the list should be a unique list if values that match the lookup_value (i.e. part numbers). This is often referred to as the “key column”. The adjacent columns in the list help form the records of related information (i.e. color, weight, price, etc…)
- Col_index_num – is the column position counting from left to right that the value to be returned is located. The first column that contains key column would be Column 1. The column directly to its right would be Column 2, and so on.
- [range_lookup] – determines whether the search will perform an exact match or an approximate match. Exact match means: if the known value is not located in the key column of the list, an error is returned. Approximate match means: if the known value is not located in the key column of the list, the closet value that is less than the known value is returned.
- VLOOKUP can only look to the right. In other words, a value to the left of the key column cannot be returned; only values in columns to the right of the key column can be seen by VLOOKUP.
- VLOOKUP will stop on the first encountered match in the key column. If duplicate items exist in the list, the first item will always be the return segment.
- Approximate match is the default posture of VLOOKUP. Although this isn’t exactly a limitation, most lookup operations require exact match logic.
- Because the col_index_num argument is typically a statically typed number, inserted or deleted columns in the table_array will cause problems.
Alternatives to VLOOKUP
The INDEX and MATCH functions, when used together, are a more powerful alternative to VLOOKUP. INDEX/MATCH does not have any of the above stated limitations.
“But if it’s so powerful, how come most people use VLOOKUP?”
The INDEX/MATCH function combination is difficult for many users to understand. Even seasoned Excel pros sometimes struggle with this approach.
Powerful; yes. Easy to use; no.
If you really want to impress the interviewer, mention the upcoming XLOOKUP function that Microsoft is poised to release. XLOOKUP will replace ALL existing lookup functions, have none of the stated limitations, and is far easier to use than any existing lookup function.