Description
The Excel VLOOKUP function searches for a value by matching on the first column of a table and returns the corresponding value from a specific column in the same row. VLOOKUP is capable of exact, approximate, and wildcard (*?) matching.
Syntax
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup])
Arguments
lookup_value – The value you want to look up
table_array – The table or range in which you want to search the lookup_value
col_index_num – The column number of the return column (starting with 1 for the left-most column)
range_lookup – [Optional] A logical value specifying whether to use exact or approximate match
Return value
A matched value from the table based on the lookup value in the first column
How to use the VLOOKUP function in Excel
The Excel VLOOKUP function searches and returns data from a table vertical orientation. The lookup_value has to appear in the first column of the the table_array that is supplied to VLOOKUP. If a match is found in the first column, VLOOKUP will return a corresponding value in the matched row from the column that you specify with the col_index_number argument. The column indexes start with 1 for the left-most column as displayed in the following image:
In this example, you can see how the lookup value 135 is searched and found in the first column. Based on what col_index_number you supply to the VLOOKUP function, either the Name (2), the Salary (3), or the Department (4) value of that row is returned.
If the same value appears multiple times in the first column of the table_array, VLOOKUP will always match on the first value it finds:
In the above example, the lookup_value “Chicago” appears multiple times in the first column. VLOOKUP matches on the first appearance and returns the corresponding Units value. All other values are ignored.
One important characteristic of VLOOKUP is that it only looks to the right, which means this function can only return values that are in a column to the right of the lookup column.
In this example, if you want to use the ID column as your first and lookup column of the table array, you are restricted to return either the corresponding Salary or Department value. We cannot lookup the corresponding Name value as this would require a lookup to the left.
Tip: If you need to do a lookup to the left, you can either use the INDEX and MATCH function together, or you can make use of the new XLOOKUP function.
Exact and approximate matching
VLOOKUP offers two modes for matching the lookup_value in the first column of the table_array. You can specify the match mode with the optional range_lookup argument:
Range Lookup Value | Match Type |
|---|---|
TRUE (default) | Approximate Match (not necessarily exact match) |
FALSE | Exact matches only |
The Exact Match mode makes sense in case you have a unique key as a lookup value, for example a unique ID for an employee. Here is a simple example:
= VLOOKUP(H4, B5:E11, 3, FALSE) // exact match
With this formula, we search for a specific ID to get the Salary of the corresponding employee. For this use case, the exact match mode is the only match mode that makes sense.
However, there are use cases in which you want to match on the next closest value and not necessarily with an exact match, like e.g. for calculating commissions. In this situation, you might make use of the Approximate Match mode and set the range_lookup value to TRUE. Here is a simple example:
= VLOOKUP(F4, B5:C11, 2, TRUE) // approximate match
In this example, VLOOKUP will match on the closest value if no exact match is found. Since $250,000 is the closest value to our lookup_value, the returned Commission Rate from column 2 is 20%.
Note: You have to sort you data by the first column in ascending order if you want to make use of the approximate match mode.
Two-Way Lookup
In most use cases, the result column index is hard-coded as a static number inside the VLOOKUP function. That makes sense, because in most cases you know what type of value you are interested in beforehand. However, there might be cases, in which you want to apply a dynamic two-way lookup, where one value is searched in the first column and another value is searched in the first row. That way, you can dynamically return one value from your table with a two-way lookup.
For this task, you can use the MATCH function to dynamically locate the right column instead of providing a static number for the column index. In the following example, we lookup a Store (in the first column) and a Year (in the first row) to identify the correct Sales value in the table:
In this example, the MATCH function searches the index of the value (provided in H5) within the first row range B4:E4. It returns a 3 which will then be used as the col_index_num inside the VLOOKUP function.
Note: A more flexible way of performing a two-way lookup is the use of either INDEX and MATCH or alternatively XLOOKUP.
Multiple Criteria
In case your data doesn’t have unique identifiers in the first column and you want to match multiple values from different columns instead, you can construct an artificial identifier by composing the values of multiple columns. This allows you to provide multiple lookup criteria for the VLOOKUP function, like in the following example:
= VLOOKUP(H4&H5, B5:E13, 4 FALSE)
In this example, there is no unique identifier the rows, so we create one by concatenating the Store and Year value into the first column. By doing this, we can then enter a lookup value for Store and another lookup value for Year into the cells H4 and H5 and combine both inside the VLOOKUP function.
Note: A more robust way of performing a lookups with multiple criteria is the use of either INDEX and MATCH or alternatively XLOOKUP.
Trap #N/A Errors if no Match is found
With VLOOKUP you will definitely face the situation that no match is found. VLOOKUP has no integrated feature to handle this situation and will return a #N/A error. The best way to trap these #N/A errors is to wrap the whole VLOOKUP function with the IFNA function. This function is specifically designed to only catch #N/A errors and return an alternative value instead.
You can use the IFNA function for the VLOOKUP like in the following example:
With this setup, the IFNA function will react as soon as the VLOOKUP functions returns an #N/A error and replace this #N/A message with a custom message like “Not found”. If you want to display no message instead, simply replace “Not found” with an empty string (“”).
Formula examples
Related Tutorials
Related Functions
Excel MATCH function
The Excel MATCH function searches for a specific item in a given range and returns the position as index number. Use MATCH and INDEX for powerful lookups.
Excel INDEX function
The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes.
Excel XLOOKUP function
The Excel XLOOKUP function searches an array, and returns an item corresponding to the first match it finds. It replaces many older lookup functions.
