VLOOKUP with Two Lookup Values

Depending on circumstances you may need to use multiple lookup values with VLOOKUP. For easing your effort, today we are going to show you how to use VLOOKUP with two lookup values. For this session, we are using Excel 2019, feel free to choose your preferred one.

First things first, let’s get to know about today’s workbook which is the base of our examples.

Dataset - VLOOKUP with Two Lookup Values

Here we have a table containing the roll of honor for the few major European football leagues. Using this dataset, we will VLOOKUP with two lookup values.

Note that this is a basic table to keep things straightforward. In practical life, you may encounter a much complex and larger dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below

Two lookup values within VLOOKUP

Since we are aiming to use two lookup values within VLOOKUP we set the example in such a way that the league name and status will be provided as lookup values to find the team name.

Criteria - VLOOKUP with Two Lookup Values

For example, we have set EPL and Champion as the lookup League and Status values respectively.

Criteria values - VLOOKUP with Two Lookup Values

Now let’s see how we can use these two values within VLOOKUP. Before that, just for a reminder, you can visit the VLOOKUP article.

1.Using Helper Column

You may need to use a helper column for using two values within VLOOKUP.

Helper column - VLOOKUP with Two Lookup Values

The value of the Helper column will be the concatenation for the two lookup values corresponding to the data table. And the approach for that can be like the one below

=C4&D4

Helper column fill formula - VLOOKUP with Two Lookup Values

C4 and D4 are the cell references for League and Status values respectively. This will join them together.

Result of helper column - VLOOKUP with Two Lookup Values

Similar to this, fill the rest of the rows for the Helper column.

Helper column Autofill - VLOOKUP with Two Lookup Values

Now we proceed to the lookup operation. Hope from the Helper column, you have sensed a bit that we will pass the two lookup values by joining together.

Yes, this will be our approach, to join the two lookup values we may use several approaches. Let’s explore them.

I. Concatenate with Ampersand

We can concatenate the two lookup values using the Ampersand (&) sign. Yes, the same way we have filled the Helper column.

Now, let’s see the lookup formula

 =VLOOKUP(H6&H7,B4:E13,4,0)

Ampersand join - VLOOKUP with Two Lookup Values

Here we have inserted the lookup values by joining them together using the ampersand sign. To see the resultant of this joining select that portion

Insights - VLOOKUP with Two Lookup Values

And press the F9 key

Insights 2 - VLOOKUP with Two Lookup Values

You will see the joining value of these two. B4:E13 is the lookup range. Make sure the lookup value can be found at the very first column of this lookup_array.

This will provide the value we want.

VLOOKUP formula result - VLOOKUP with Two Lookup Values

We can change the lookup values to see the change in the result.

Change of values - VLOOKUP with Two Lookup Values

II. Concatenate with CONCAT Function

We can use a function called CONCAT to join the lookup values. The CONCAT function combines the text from multiple ranges. To know more about the function, visit the Microsoft Support site.

The formula will be quite similar to the previous one, the only change will be the joining approach of the two lookup values.

=VLOOKUP(CONCAT(H6,H7),B4:E13,4,0)

This time we have set the two values within the CONCAT function.

VLOOKUP - CONCAT - VLOOKUP with Two Lookup Values

This function joins the two values and then the rest of the mechanism will be the same. We will get the result (the team name depending on the criteria).

VLOOKUP - CONCAT formula result - VLOOKUP with Two Lookup Values

Again, we can change the lookup values and will find an updated result at the Team field.

Change of lookup values - VLOOKUP with Two Lookup Values

III. Concatenate with TEXTJOIN Function

Another approach of joining can be the use of the TEXTJOIN function. The TEXTJOIN function concatenates multiple values together with or without a delimiter.

The syntax for the function is

TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...)

delimiter: Separator between each text.

ignore_empty: Whether to ignore empty cells or not.

text1: First text item or range to be joined.

text2: Second text item or range to be joined. This is an optional field.

For further information, visit the Microsoft Support site.

Now we are going to use the TEXTJOIN function to join our two lookup values. Since we don’t need any delimiter we will leave the delimiter field empty.

The formula will be

=VLOOKUP(TEXTJOIN(,TRUE,H6,H7),B4:E13,4,0)

VLOOKUP- TEXTJOIN - VLOOKUP with Two Lookup Values

Within TEXTJOIN we have used TRUE at the ignore_empty field to ignore empty cells.

This TEXTJOIN concatenates the lookup values together and then the VLOOKUP function performs its operation to provide the final output.

VLOOKUP- TEXTJOIN - VLOOKUP with Two Lookup Values

Here the formula provided the team name having the Champion status from the league EPL.

Feel free to change the lookup values to clarify the formula.

Change of Lookup values 3- VLOOKUP with Two Lookup Values

2. Using Helper Function

Having an extra column may not be an ideal one always. Rather we can use a helper function along with VLOOKUP to complete the task.

Helper function - VLOOKUP with Two Lookup Values

Since our aim is to perform the lookup without any helper column we will use a function called CHOOSE.

The CHOOSE function returns a value from a list using a given position or index. And the syntax of this function is

CHOOSE(index_num, value1, [value2], ...)

index_num: A number between 1 to 254, specifies the value argument.

value1: A value from which to choose.

value2: Second value to choose. This is an optional field.

To know more about the function please visit the Microsoft Support site.

Now let’s write the formula to pass two lookup values and the formula will be

=VLOOKUP(G6&G7,CHOOSE({1,2},$B$4:$B$13&$C$4:$C$13,$D$4:$D$13),2,0)

VLOOKUP - CHOOSE formula - VLOOKUP with Two Lookup Values

The CHOOSE portion of this formula works as a virtual helper table. We have used 1 and 2 (within the curly braces) as the index number.

Then we have concatenated the League and Status column together, this will be the first column of our virtual table.

Here we have inserted the Team column in the value2 field and this will be the second column of the virtual table.

To visualize, select the CHOOSE portion

CHOOSE portion - VLOOKUP with Two Lookup Values

And press F9, you will get the insights

CHOOSE portion insights - VLOOKUP with Two Lookup Values

Hope you have understood this, the two columns (League – Status join and Team) of the virtual table are separated by a comma here.

This table becomes the lookup_array for the VLOOKUP function. Since our desired result would be found at the second column from the virtual table we have used 2 as the column_number.

Press CTRL + SHIFT + ENTER to execute the formula as it is an array formula. VLOOKUP - CHOOSE formula result - VLOOKUP with Two Lookup Values

Change the lookup values to observe the changes in the result.

Change of lookup values 4 - VLOOKUP with Two Lookup Values

Note that to concatenate the lookup values, you can use the CONCAT or TEXTJOIN function instead of ampersand. 

Conclusion

That’s all for today. We have shown how to use VLOOKUP with two lookup values. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other approaches that we might have missed here.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo