In a long dataset, we often need to look up values with multiple criteria. In this article, we will utilize **VLOOKUP** Function with two lookup values to filter result criteria in Excel. Also, we will be looking at two alternative approaches to look up values in a dataset using some known functions.

## 📁 Download Excel File

Download the Excel file below.

## Overview of VLOOKUP Function

The syntax of **the VLOOKUP Function** is as follows:

**=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])**

Where** lookup_value** (Required) – The value we need to search for.

**table_array** (Required) – The range of cells where we need to search for the lookup value and look for a match.

**column_index_num** (Required) – is the number of the column from which to return a value.

**Range_lookup **(Optional) – The number of column from which the function needs to return a value.

## Learn to Use VLOOKUP Function with Two Lookup Values with These 2 Approaches

To **use VLOOKUP Function** in Excel with two lookup values to find output, we can follow two distinct steps. The first step has three substeps for different concatenating ways. Suppose, we take a dataset of various console products, their companies, and their lifetime sales amount in millions. We will be using this data to demonstrate the different approaches. The dataset is shown as follows:

**Approach**

### 1. Utilizing Helper Column

As the **VLOOKUP** Function allows us to look for one certain value inside a database and not multiple values or criteria, we need to use a helper column to merge the data of two columns. The steps for this approach to combine the Product and the Company are as follows:

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, let us insert a column on the left of the Product column and name it as Helper Column. To do this, we right click and select
**Insert**.

- Then, a box will appear and we check the entire column in the box and press
**OK**.

- We select cell
**B6**, which is the first cell of the Helper Column, and write the given formula to combine two-column data.

`=C6&D6`

- After that, we drag the
**Fill Handle**icon up to the cell.

- The results table will look like the following image.

- Below the table, we create three sections named Product, Company, and Sales and write a product and a company name in the cells
**D16**and**D17**.

- Our goal is to use
**VLOOKUP**Function to find out the Lifetime sales of a product of a company from the dataset table above. We can use the**VLOOKUP**Function to concatenate two cell values in 3 different ways. These are explained below:

**📕 Read More: Find MAX of Multiple Values Using VLOOKUP Function in Excel**

**Utilizing Helper Column**

#### 1.1 Concatenating with Ampersand

The Ampersand is a calculation operator by which we can join text items without using any function. We denote this operator by **&** sign.

⬇️⬇️ **STEPS **⬇️⬇️

- We select cell
**D18**and move over to the formula tab. - After that, we write the following formula.

`=VLOOKUP(D16&D17,B6:E14,4,FALSE)`

**🔨 Formula Breakdown**

**VLOOKUP(D16&D17,B6:E14,4,FALSE)**

👉 Here **D16&D17** merges the values of the cell **D16** and **D17** by the ampersand (**&**) sign.

👉** B6:E14** indicates the cell range in which we have to look for a value. The lookup value has to be found in the very first column of the lookup range.

👉 **4** indicates the number of columns of the selected cell range.

👉** FALSE** denotes the return value to be an exact match.

- The results will be like that of the following table.

**Utilizing Helper Column**

#### 1.2 Concatenating with** CONCAT** Function

The basic syntax of the **CONCAT** Function is given below:

**CONCAT(text1, [text2], …)**

where,** text1** = first text value, cell reference or cell range.

**text2** = second text value, cell reference or cell range.

⬇️⬇️ **STEPS **⬇️⬇️

- In addition, like a similar approach to the previous one, we select cell
**D18**and move over to the formula tab. - After that, we write the given formula.

`=VLOOKUP(CONCAT(D16,D17),B6:E14,4,FALSE)`

**🔨 Formula Breakdown**

**VLOOKUP(CONCAT(D16,D17),B6:E14,4,FALSE)**

👉 Here **CONCAT(D16,D17)** concatenates value of the two cells **D16** and **D17**.

👉 **B6:E14** indicates the cell range in which we have to look for a value.

👉** 4** indicates the number of columns of the selected cell range.

👉** FALSE** denotes the return value to be an exact match.

- The results will be like that of the following table.

**Utilizing Helper Column**

#### 1.3 Concatenating with **TEXTJOIN** Function

The basic syntax of the **TEXTJOIN** Function is as follows:

**TEXTJOIN (delimiter, ignore_empty, text1, [text2], …)**

**delimiter (Required)**: A text string, either empty or one or more characters enclosed by double quotes.

**Ignore_empty (Required)**: If TRUE, ignores empty cells.

**text1 (Required)**: First text item to be joined.

**text2 (Optional)**: Additional text items to be joined.

⬇️⬇️ **STEPS **⬇️⬇️

- Like the previous methods of concatenating, in this method, we select the cell
**D18**first and then move over to the formula tab. - Next, we write the following formula.

`=VLOOKUP(TEXTJOIN(,TRUE,D16,D17),B6:E14,4,FALSE)`

**🔨 Formula Breakdown**

**VLOOKUP(TEXTJOIN(,TRUE,D16,D17),B6:E14,4,FALSE)**

👉 Here **TEXTJOIN(,TRUE,D16,D17)** joins value of the two cells** D16** and **D17**.

👉 **B6:E14** indicates the cell range in which we have to look for a value.

👉 **4 **indicates the number of columns of the selected cell range.

👉 ** FALSE** denotes the return value to be an exact match.

- The output will be like that of the following table.

**📕 Read More: VLOOKUP to Find Duplicates in Two Columns in Excel**

**Approach**

### 2. Combining with **CHOOSE** Function

In this kind of approach, we don’t need the use of any helper columns. Therefore, the Function that we’ll be using is **CHOOSE**. The basic syntax of this function is as follows:

**CHOOSE(index_num, value1, [value2], …)**

**Index_num (Required)**: The position of the value to return. It can be any number from 1 to 254 or a cell reference or another formula.

**value1(Required)**: A value from the list of 1 to 254 from which to choose.

**value2(Optional)**: The second value to choose from a list of 254 values.

The steps for this process are as follows:

⬇️⬇️ **STEPS **⬇️⬇️

- First, we select cell
**D18**. - Now, we move over to the formula tab and write the following formula.

`=VLOOKUP(C16&C17,CHOOSE({1,2},$B$6:$B$14&$C$6:$C$14,$D$6:$D$14),2,0)`

**🔨 Formula Breakdown**

**VLOOKUP(C16&C17,CHOOSE({1,2},$B$6:$B$14&$C$6:$C$14,$D$6:$D$14),2,0)**

👉 Here, **C16&C17** merges the value of **C16 **and **C17** by the ampersand (**&**) sign.

👉 In the **CHOOSE** Function, 1 and 2 are index numbers.

👉 **The CHOOSE Function** does two things. First of all, they combine the values from **B16:B14 **with **C6:C14** and then give the output as a single array.

👉 Lastly, **0** or **FALSE** denotes the return value which gives output as an exact match.

- After that, we press
**Ctrl+Shift+Enter**as it is an array formula. The cell should not be a merged cell. - Therefore, the sale output for a specific product and company will be like the following table:

**📕 Read More: 7 Ways to Use VLOOKUP to Compare Two Lists**

## Alternative Ways to LOOKUP Values Instead of Using VLOOKUP Function

This section is a bonus for you. Here, we would like to use different functions to get the lookup values in Excel. At first, we will utilize the **SUMPRODUCT** function. Then, we will apply the combination of **INDEX** and **MATCH** functions to get the desired answer which is similar to the **VLOOKUP** function.

**Approach**

### 1. Applying **SUMPRODUCT** Function

**The SUMPRODUCT function** is another alternative way of looking up two values and delivering an output. However, if the resulting output we want is text, then this method cannot be used. The steps for this process are as follows:

⬇️⬇️ **STEPS **⬇️⬇️

- First, we select cell
**D18**. - Upon selecting the cell, we hover over to the formula tab and write the following formula.

`=SUMPRODUCT((B6:B14=C16)*(C6:C14=C17),D6:D14)`

**🔨 Formula Breakdown**

**SUMPRODUCT((B6:B14=C16)*(C6:C14=C17),D6:D14)**

👉 Here,** B6:B14 **is the first array argument from where we specify the product name from cell **C16**.

👉 **C6:C14** is also the first array argument from where we specify the company name from the cell **C17**.

👉 **D6:D14** is the second array argument where range of sales are present and from where we will get our output.

- After that, we press
**Ctrl+Shift+Enter**as it is an array formula. Besides that, the cell should not be a merged cell. - Therefore, the sale output for a specific product and company will be like the following table:

**📕 Read More: What Is Table Array in VLOOKUP in Excel?**

**Approach**

### 2. Combining INDEX and MATCH Functions

The combination of **INDEX** and **MATCH** functions gives us another way of performing LOOKUP with two lookup values. We can use this approach by following these steps.

⬇️⬇️ **STEPS **⬇️⬇️

- At first, we select cell
**D18**. - Then we write the following formula in the formula tab.

`=INDEX(D6:D14,MATCH(1,(B6:B14=C16)*(C6:C14=C17),0))`

**🔨 Formula Breakdown**

**INDEX(D6:D14,MATCH(1,(B6:B14=C16)*(C6:C14=C17),0))**

👉 Here, **B6:B14** asks Excel to find out if **B6=C16** and subsequently does that for all other entries of the same column up to B14. If the result matches, the return value is TRUE.

👉 **C6:C14** asks Excel to find out if **C6=C17** and subsequently does that for all other entries of the same column up to C14. If the result matches, the return value is TRUE.

👉 Thus, the product of two TRUE values will be TRUE or 1 and for the rest combinations, the value will be 0. Only TRUE*TRUE= 1.

👉 Here 1 is the lookup value and 0 is the match type.

- After that, we press
**Ctrl+Shift+Enter**and the cell where we want our output must not be merged. - Finally, the result will be visible in the following image below:

## 📄 Important Notes

🖊️ For array formulas, we must certainly press **Ctrl+Shift+Enter** and the cell where we write the formula should not be merged.

🖊️ As a matter of fact, the **SUMPRODUCT** Function cannot be used to look up values if the value that needs to be obtained is a text value.

🖊️ Basically,** VLOOKUP** Function can only look for one value within a database, not multiple values.

## 📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌 ** **Helper columns are indeed necessary to look up two values using **VLOOKUP** Function as the function can look for one value within a dataset.

📌 Alternatively, instead of a helper column, we can also use a helper function named **CHOOSE** Function to look up two values from a dataset in Excel.

📌 Alternate approaches to looking up two values can include **SUMPRODUCT** Function and using a combination of **INDEX MATCH** Functions. **SUMPRODUCT** Function has a limitation to texts in this regard.

## Conclusion

In conclusion, I hope this article will give you a good idea about using **VLOOKUP** Function to look for two lookup values. Additionally, this article also explains alternative approaches to looking up two values without **VLOOKUP** Function. If you have any queries then feel free to comment below. Follow our webpage **Excelden** for more Excel-related articles and information.

## Related Articles

**3 Quick Ways to Vlookup and Return Multiple Values Horizontally****5 Examples to Use IF and VLOOKUP as Nested Function in Excel****8 Ways to VLOOKUP to Return Multiple Values Vertically in Excel****6 Solutions If VLOOKUP Returns #N/A When Match Exists in Excel****5 Ways to Extract Filtered Data into Another Excel Sheet**