2 Ways to Use VLOOKUP Function with Two Lookup Values

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:

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:

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.

• 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

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.

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

The basic syntax of the CONCAT Function is given below:

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.

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

The basic syntax of the TEXTJOIN Function is as follows:

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.

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

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

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:

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.

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

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.

• 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?

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.

• 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