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:

=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:

vlookup with two lookup values sample dataset


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.

vlookup with two lookup values utilizing helper column

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

vlookup with two lookup values by using helper column and concatenating with ampersand

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

vlookup with two lookup values by using helper column and concatenating with CONCAT Function

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

vlookup with two lookup values by using helper column and concatenating with TEXTJOIN Function

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

vlookup with two lookup values by using CHOOSE helper function

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

SUMPRODUCT Function with two lookup values

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

INDEX MATCH Combination to find two lookup values

  • 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

(Visited 25 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo