How to Perform VLOOKUP with Two Lookup Values in Excel

To perform a VLOOKUP with two lookup values in Excel, you need to modify the traditional VLOOKUP function by using a helper column or by combining the VLOOKUP with other functions. Here are two methods to perform a VLOOKUP with two lookup values in Excel:

Method 1: Using a Helper Column

1. Open your Excel workbook and make sure your data is properly arranged. For example, here's a table with two lookup values (Product and Color) and the corresponding Price:A B C

1 Product Color Price 2 Apple Red 10 3 Apple Green 12 4 Orange Red 15 5 Orange Green 17

2. Add a helper column that combines the two lookup values. In this example, insert a new column D and enter the formula =A2&"-"&B2 in cell D2. This will concatenate the Product and Color values with a hyphen.A B C D

1 Product Color Price Helper 2 Apple Red 10 Apple-Red 3 Apple Green 12 Apple-Green 4 Orange Red 15 Orange-Red 5 Orange Green 17 Orange-Green

3. Copy the formula in cell D2 down to the other cells in the helper column.
4. Now, you can use the VLOOKUP function with the helper column as the lookup value. For example, to find the price of an Apple that is Red, use the formula =VLOOKUP("Apple-Red", D2:E5, 2, FALSE).

Method 2: Using VLOOKUP with INDEX and MATCH functions

1. In this method, we don't need a helper column. Use the same example data as before:A B C

1 Product Color Price 2 Apple Red 10 3 Apple Green 12 4 Orange Red 15 5 Orange Green 17

2. Use the VLOOKUP function in combination with the INDEX and MATCH functions. For example, to find the price of an Apple that is Green, use the following formula:

=VLOOKUP(INDEX(A2:B5,MATCH("Apple",A2:A5,0),1),A2:C5,3,FALSE).

In this formula, the INDEX function returns the value of the first cell where "Apple" is found in the range A2:A5, and the MATCH function returns the relative position of the first occurrence of "Apple" in the same range. The VLOOKUP function then uses this value to find the corresponding price in the range A2:C5.

These methods will allow you to perform a VLOOKUP with two lookup values in Excel.