#### 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

**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**

*table_array***argument. The column indexes start with 1 for the left-most column as displayed in the following image:**

*col_index_number*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 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.

### 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.