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, VLOOKUP 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.