# How to Use VLOOKUP Function/Formula in Excel with 18 Examples

## Purpose VLOOKUP Function in Excel

Vertical lookup, denoted by the abbreviation **VLOOKUP**, refers to a function that looks up a value in a column of a table before returning the value from a different column that is provided to the right of the lookup column.

## Syntax of VLOOKUP Function

`=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)`

## Argument of VLOOKUP Function

*lookup_value: **The value in the data table’s lookup column that you want to search for*

*table_array: **The set of cells that make up the data table.*

*col_index_num:** The starting column number from which you want to obtain the data. This column should be in the right position in the lookup column.*

*range_lookup:** You can use the logical value range lookup to specify whether you want an exact match or an approximation. *

*For an exact match, this argument is**FALSE**or**0**.**For an approximate match, this argument is**TRUE**or**1**.**If the**range_lookup**is omitted, then the**VLOOKUP**function will take the argument as***TRUE**

## Return of VLOOKUP Function

Based on a lookup value that is located in the table’s lookup column, the **VLOOKUP** function returns the value of a cell that is located in a specific column. According to the data contained in the table, the returned value may be a logical value, a text value, or a number.

## Type A. Behaviour of the VLOOKUP Function:

### Example #1: VLOOKUP Function to Find Exact Match

Assume you have a table with the prices of various products and want to identify the price of a specific product based on its** ID**.

Apply the following formula in the output cell **G3**

`=VLOOKUP(G2,B2:D7,3,FALSE)`

Explanation:

**G2:**This input field holds the**ID**of the product for which you are looking for the price.**C2:D7:**The**IDs**of the products and the corresponding prices are located in this range of cells.**3:**This indicates that you wish to return the value found in the table’s third column(from left), which lists the prices.**FALSE**: This indicates that you seek an exact match for the product name.

In this case, the product** ID** is the lookup value, and the** VLOOKUP **function looks for it in the table’s first column. The **VLOOKUP** function returns the relevant values from the column number specified in the syntax.

### Example #2: VLOOKUP Function to Find Approximate Match

With reference to **Example #1**, we just input **TRUE** as the ** range_lookup** instead of

**FALSE**in the syntax of the

**VLOOKUP**function to find an approximate match.

` =VLOOKUP(G2,B2:D7,3,TRUE)`

When we provided **TRUE** as the fourth option, the **VLOOKUP** function will search the first column of the table for a value that is the nearest lower than the lookup value.

### Example #3: VLOOKUP Function to Right Lookup

Vlookup always looks from left to right. So, the third argument, ** col_index_num**, must be located on the right side of the first column

**B**in the range

**B2:D7**.

### Example #4: VLOOKUP Finds First match

If you look for a product that is enlisted in the product list multiple times, **VLOOKUP** will take the first one.

### Example #5: VLOOKUP has Case-Insensitivity

By default, the **VLOOKUP** function is not case-sensitive.

See the image below: **VLOOKUP** returns the same result even when the lookup value is case-sensitive.

### Example #6: Partial Match by VLOOKUP

Using wildcards like** asterisks (*) **or **question marks (?)** in the lookup value or the table will allow you to execute a partial match.

Apply the formula below to find a partial match

`=VLOOKUP("*"&F3&"*",B2:C7,2,FALSE)`

**Enter** the part of the name you want to search for, like “**Mi**” in cell **F3**.

The **asterisk (*)** is used as a wildcard in the **VLOOKUP** formula to match any text before or after the partial name. For instance, the formula searches for the value in cell **F3** (which contains “**Mi**“) within the first column of the table **B2:C7**, allowing for any text before or after “**Mi**“

## Type B. Table Array in VLOOKUP

### Example #7: Application of VLOOKUP for Table Array

The formula shown in the preceding example can be made simpler if you construct a table with the cell range **B2:D7** and rename it **Table2**.

`=VLOOKUP(G2,Table2,3,FALSE)`

This formula gives the same output mentioned in Example #1. Here, just the data range **B2:D7** is replaced by the table name **Table2**

### Example #8: IFERROR and VLOOKUP Combined Formula To Lookup in Tables Located in the Same Sheet in Excel

In order to find a value in two distinct tables, the **IFERROR** function with two **VLOOKUP** functions can be applied in a single formula.

Suppose you have two tables named **Table_X** and **Table_Y** in the same Excel sheet. Both tables contain different products and prices with individual **IDs**.

You can apply the following formula to find the price of products for any given **ID**.

`=IFERROR(VLOOKUP(C11,Table_X,3,0),IFERROR(VLOOKUP(C11,Table_Y,3,0),"Not Found"))`

Explanation:

- Errors that could happen during the lookup process are handled using the
**IFERROR**function. The first parameter to the**IFERROR**function is the formula to evaluate, and the second argument is the value to return if the formula produces an error. - If the first
**VLOOKUP**function fails to find the**ID**in**Table_X**then the**#N/A**error is returned and the**IFERROR**function triggers the Second**VLOOKUP**function to continue the search in the**Table_Y** - The value “
**Not Found**” which is the second argument of the outer**IFERROR**function, is returned if the second**VLOOKUP**function likewise returns an error.

### Example #9: VLOOKUP Formula to Lookup Between Two Different Sheets in Excel

In this example, **Table_A** and **Table_B** are located on different sheets.

You want to return the prices of products in **Table_B** from **Table_A**.

To obtain your purpose just modify the formula we already used

`=VLOOKUP(B3, Table_A, 2, FALSE)`

Explanation:

- The first argument is the lookup value, which is located in cell
**B3**. The**VLOOKUP**function will search for this value in the first column of “**Table_A**“(Located in the**Price**sheet) - The table array, designated as “
**Table A**” is the second argument. The first column of this table’s data comprises the lookup values, and the second column provides the corresponding values to be returned.

### Example #10: IFERROR and VLOOKUP Combined Formula to Lookup in Different Workbooks

Even so, you can use the following formula to search the tables in the various sheets.

`=VLOOKUP(B3, [vlookup.xlsx]Sheet1!Table1, 2, FALSE)`

This formula finds the value of cell **B3** from** Table1** which is located in the **Sheet1** of workbook named **vlookup.xlsx **

## Type C. VLOOKUP Nested with Other Functions

### Example #11: Nested (Double) VLOOKUP Formula

Suppose you have two tables named **Table_C** and **Table_D**. **Table_C** contains the product name and corresponding** ID**, and **Table_D** contains product prices with corresponding **IDs**.

You want to create a new table that contains the Product Name and the corresponding Prices.

Apply the following formula in the output cell:

`=VLOOKUP(VLOOKUP(B3,Table_C,2,0),Table_D,2,0)`

Explanation:

- The first argument of the outer
**VLOOKUP**function is another**VLOOKUP**function, which is used to look up a value in “**Table_C**” based on the value in cell**B3**. - The outcome of the inner
**VLOOKUP**function serves as the first input for the outer**VLOOKUP**function. This implies that the outer**VLOOKUP**function will search the first column of “**Table D**” for the value returned by the inner**VLOOKUP**function. - The outer
**VLOOKUP**function returns the corresponding value from “**Table D’s**” second column if a match is detected. Otherwise, the**#N/A error**returned.

### Example #12: VLOOKUP Function Excel with Multiple Criteria

Suppose you have a table containing the product Category, Name, and Prices

You want to create a formula to lookup formula to return prices based on two inputs( eg. Category and Name)

To achieve your objective, follow steps:

**Step 01:** Insert a new column on the left side of the table named “**Helping Column**.”

**Step 02:** Enter the following formula in cell **B2**. Press** Enter**. Then **AutoFill** the remaining cells in the helping column** B**

`=C2&D2`

**Step 03: **Apply the following formula in the output cell **H4**

`=VLOOKUP(H2&H3,B2:E7,4,0)`

**Explanation:**

**H2&H3**is the**VLOOKUP**function’s first argument. The values in cells**H2**and**H3**are combined using the**&**symbol. As a result, this argument generates a single lookup value that combines the data in cells**H2**and**H3**.- Following a search for a concatenated value in a table’s first column, the
**VLOOKUP**function returns the corresponding value from the table’s fourth column.

### Example #13: MATCH and VLOOKUP Formula in excel for multiple criteria

You can apply the **MATCH** function nested with **VLOOKUP** to perform a more extended lookup.

Suppose you want to return the Name, Quantity, or price based on two input values (eg. Category and Price, or Category and Name, Or Category and Quantity)

Apply the following formula in the output cell **H4**

`=VLOOKUP(H2,B2:E7,MATCH(G3,B1:E1,0),0)`

Explanation:

- The
**MATCH**function looks for the value in cell**G3**in the row range**B1:E1**when the formula is evaluated. It then returns the column number of the cell where the value was located and is injected into the syntax of the**VLOOKUP**Function. - The
**VLOOKUP**function then looks for the value in cell**H2**in the first column of the range**B2:E7**. If a match is found, the function returns the corresponding value from the column of the table that corresponds to the column where the value in cell**G3**is located

### Example #14: IF and VLOOKUP Formula in Excel to Check Multiple Criteria

You can use the IF function in conjunction with the **VLOOKUP** function to check multiple criteria.

`=VLOOKUP(H2, IF(C2:C7=H3, B2:E7, ""), 4, FALSE)`

Explanation:

- An IF function is the
**VLOOKUP**function’s second argument. Each value in the range**C2:C7**is compared to the value in cell**H3**using the**IF**function. The**IF**function returns the matching row from columns**B**to**E**(i.e., the entire row) of the table as an array if a value in**C2:C7**is equal to**H3**. The**IF**function returns an empty string if any of the values in**C2**through**C7**are not equal to**H3**(i.e., “”). - Then the
**VLOOKUP**function runs a search for the value entered in cell**H2**in the returned row of the**IF**function and returns the price.

### Example #15: CHOOSE and VLOOKUP Function to Perform the Left Lookup

Unlikely other lookup functions in Excel, **VLOOKU**P canâ€™t perform a search on the left side of the lookup column. We can employ the** CHOOSE** function to elevate the usefulness of the **VLOOKUP** function.

Suppose, from the data table given below, you want to return the product category based on the princess.

Apply the following formula

`=VLOOKUP(G3,CHOOSE({1,2},D2:D7,B2:B7),2,0)`

**Explanation:**

- The
**CHOOSE**function returns a new table with the specified columns in the sequence provided in its first argument. In this instance, column**D**should be added first, then column**B**, to a new table. Therefore, the array**{1, 2}**is used as**CHOOSE’**s first parameter. - The actual order of columns
**D**and**B**are swapped by the**CHOOSE**function. Now, it’s usable for the**VLOOKUP**function. The**VLOOKUP**Function returns the value corresponding to the cell**G3**from column**B**

### Example #16: IFS with VLOOKUP Function with Multiple Criteria

Let’s say you want to offer a **15%** discount on items costing more than **$500**. You need to automatically calculate discounts and return the costs based on the category.

`=IFS(VLOOKUP(H2,B2:E7,4,0)>500,VLOOKUP(H2,B2:E7,4,0)*0.85,VLOOKUP(H2,B2:E7,4,0)<500,VLOOKUP(H2,B2:E7,4,0))`

Explanation:

- The
**VLOOKUP**function is used to look up the value in cell**H2**in the data range**B2:E7**and return the value in the fourth column of that table. - The
**VLOOKUP**function’s value is examined by the**IFS**function against the following two criteria:

a. The formula multiplies the value by **0.85** and returns the result if it is higher than **500**.

b. The formula returns the value exactly as entered if the value is less than **500**.

- The
**IFS**function gives an error if neither criterion is true.

### Example #17: VLOOKUP Formula in Excel to Compare Values Between Two Columns

The following table contains a list of studentsâ€™ names and a list of passed students.

Suppose you want to filter only the passed student names from the list in column B.

You can apply the following formula:

`=IFNA(VLOOKUP(C2, $B$2:$B$14, 1, FALSE), "")`

**Explanation:**

- If the lookup value cannot be found in the table, the
**VLOOKUP**function will return a**#N/A**error value. - The
**#N/A**mistake is detected by the**IFNA**function, which then replaces it with a blank cell**(by the empty string “”)**.

### Example #18: VLOOKUP with INDIRECT Function for Dynamic Lookup Range

You can dynamically refer to a cell or range of cells whose placement is determined by the contents of another cell by using the **INDIRECT** function. This is helpful when you want to refer to a cell or range of cells on a different worksheet or when you want to make a dynamic formula that refers to different cells based on changing inputs, among other uses.

Suppose you have two tables named **Price1** and **Price2** that contain various products and prices.

You want to create a new table containing all products and prices from two tables. If you use the **VLOOKUP** formula alone, you have to write or modify the formula twice.

Let’s see the following formula

`=VLOOKUP(B2,INDIRECT($F$3),2,0)`

Just enter the table name in the input cell **F3 **and see the following result

For** D1:D7**, **VLOOKUP** returns **#N/A** because the corresponding products are not found in the table named Price2.

See the output, if we enter **Price1 **in cell **F3**

**Explanation:**

The **VLOOKUP** function’s second input is **INDIRECT($F$2)**. The text string in cell **F2** is changed into a valid reference to a cell, table, or range of cells on the worksheet using the **INDIRECT** function.

## Things to Keep in Mind about VLOOKUP Function

**#N/A error**: When the lookup value cannot be found in the first column of the table or range, the**VLOOKUP**function returns a**#N/A error**. Make sure the lookup value is spelled correctly and that it is present in the table or range in order to correct this mistake.**#REF! error**: When a table or search region is deleted or moved, the**VLOOKUP**function encounters a**#REF! error**. Update the range reference in the**VLOOKUP**function to reflect the changed location of the table or range to correct this mistake.**#VALUE! error**: When the arguments in the**VLOOKUP**function are invalid, the**#VALUE!**error is generated. This error, for instance, might happen if the parameter for the column number is not a number. Verify the**VLOOKUP**function’s inputs once more to be sure they are accurate.

## VLOOKUP VS HLOOKUP

The Excel functions** VLOOKUP** and **HLOOKUP** both let you search for a value in a table and return a corresponding value in the same row or column.

The following are some other variations between **VLOOKUP** and **HLOOKUP**:

- Whereas
**HLOOKUP**can only search for values in the first row of a table,**VLOOKUP**(Vertical Lookup) can only look for values in the first column of a table. - Only the values to the right of the lookup column can be returned by
**VLOOKUP**, whereas the values below the lookup row can only be returned by**HLOOKUP**(Horizontal Lookup). - Given that Excel reads columns of data more quickly than rows of data,
**VLOOKUP**typically performs more quickly than**HLOOKUP**, especially for large tables.

## XLOOKUP VS VLOOKUP

**VLOOKUP** has been available since Excel’s early versions, but **XLOOKUP** is a relatively recent function that was released in 2019. **VLOOKUP** is intended to be replaced by the more capable and adaptable **XLOOKUP** function, which has a number of advantages over the earlier function.

The following are some significant variations between** XLOOKUP** and **VLOOKUP**:

- Compared to
**VLOOKUP**,**XLOOKUP**is far more adaptable because it can look up values anywhere in a table or range and return results from any column or row. This means that, unlike**VLOOKUP**,**XLOOKUP**allows you to look up data that is to the left of the lookup column. **XLOOKUP**has built-in error handling features, so if a lookup value cannot be located, you can define a specific error message or value to be shown.**VLOOKUP,**in contrast, simply issues the**#N/A**error when a lookup value cannot be located.

## VLOOKUP VS INDEX MATCH

Excel features like **VLOOKUP** and** INDEX MATCH** let you search for a value in a table and have the equivalent value returned in the same row or column. Nonetheless, many Excel users favor** INDEX MATCH** over** VLOOKUP** because it is typically regarded as being more powerful and flexible.

The following are some significant distinctions between **VLOOKUP** and **INDEX MATCH**:

- Compared to
**VLOOKUP**,**INDEX MATCH**is far more adaptable because it can look up values anywhere in a table or a range and return results from any column or row. In contrast to**VLOOKUP**, which cannot look up data above the lookup row or to the left of the lookup column, you can look up data in these locations using**INDEX MATCH**. - When looking across huge tables or ranges,
**INDEX MATCH**may occasionally be quicker than**VLOOKUP**. This is because**INDEX MATCH**, which may be faster than**VLOOKUP**‘s approximate matching technique, employs the**MATCH**function to determine the location of the lookup value in the table or range. **VLOOKUP**can provide an estimated match if an exact match cannot be discovered because it is built to operate with approximate matches. This lack of capabilities in**INDEX MATCH**can be both a benefit and a drawback, depending on the particular use case.