How to Use VLOOKUP with Two Lookup Values in Excel

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.

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.

In the following, I have described 3 quick and easy methods to VLOOKUP with two lookup values.

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.

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

Now let’s see how we can use these two values within VLOOKUP.

1. Inserting a Helper Column to Use VLOOKUP with Two Lookup Values in Excel 

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

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

• C5 and D5 are the cell references for League and Status values respectively. This will join them together.

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

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

1.1 Concatenate with Ampersand

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

Steps:

• Presently, choose a cell (H6) and write the below formula down-

Where,

• We have inserted the lookup values by joining them together using the ampersand sign.
• You will see the joining value of these two. B5:E14 is the lookup range. Make sure the lookup value can be found at the very first column of this lookup_array.

• Next, press ENTER key from the keyboard.
• Within a moment, we have successfully extracted our output with two lookup values using the VLOOKUP Simple isn’t it?

Read More: How to VLOOKUP and Return Multiple Values Vertically in Excel

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

Steps:

• First, select a cell (H6) and write the below formula down-

Where,

• The CONCAT 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).

• Hence, click ENTER to get the output.
• Finally, we have the lookup value in our hands.

Read More: Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)

1.3 Concatenate with TEXTJOIN Function

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

Steps:

• To start with, choose a cell (H6) and write the below formula down-

Where,

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

• Thereafter, hit the ENTER key to get the final output.
• Within a blink of an eye, we will get our desired output.

• If we change the inputs from  “League” and “Status” section the output will be changes according to the given conditions.

Read More: How to Use VLOOKUP to Search Text in Excel (4 Ideal Examples)

Similar Readings

• Excel LOOKUP vs VLOOKUP: With 3 Examples
• Why VLOOKUP Returns #N/A When Match Exists (with Solutions)
• How to VLOOKUP and Return Multiple Values Vertically in Excel
• SUMIF and VLOOKUP Functions Across Multiple Sheets in Excel
• How to VLOOKUP and SUM Across Multiple Sheets in Excel

2. Combining VLOOKUP with CHOOSE Function for Two Lookup Values

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.

Steps:

• Similarly, choose a cell (G6) and write the below formula in the cell-

Where,

• The CHOOSE function returns a value from a list using a given position or index. 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, which will be the virtual table’s second column.
• This table becomes the lookup_array for the VLOOKUP Since our desired result would be found at the second column from the virtual table we have used 2 as the column_number.

• Therefore, hit ENTER.
• In summary, we have the lookup result in our hands without helper column.

Read More: VLOOKUP and Return All Matches in Excel (7 Ways)

3. Use of MATCH Function with VLOOKUP for Two Lookup Values in Excel

In some cases, you can also use the MATCH function with the VLOOKUP function to find your desired output with two lookup values.

Steps:

• First, select a cell (G6) and apply the formula from below-

Where,

• The MATCH function will match a specific value provided in cell (G5) inside an array (B4:D4).
• Thereafter, the VLOOKUP function will lookup for a value which is “EPL” from the given range (B5:D14).

• Next, click ENTER.
• Here, we have the final lookup value in our hands. Simple isn’t it?

Read More: VLOOKUP with Multiple Criteria in Excel (6 Examples)

Combination of INDEX and MATCH Functions for Two Lookup Values

If you are looking for a solution to find your desired value without using the VLOOKUP function then you are at the right place. With the combination of INDEX and MATCH functions you can find your desired output in a simple way.

Steps:

• In the same fashion, select a cell (G6) and apply the formula from below-

Where,

• The INDEX-MATCH function works as a two-way lookup. Thus the MATCH function will lookup for values are given in cells (G4) and (G5) from the range (B5:C5:C14).
• Next, the INDEX function will retrieve the value at the given location from a range (D5:D14).

• Simply, click ENTER.
• In conclusion, the result is “Manchester United” from two lookup values “EPL” and “Runner Up”.

Read More: INDEX MATCH vs VLOOKUP Function (9 Practical Examples)

Download Practice Workbook

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

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.

Further Readings

• How to Use Named Range in Excel VLOOKUP Function
• VLOOKUP Example Between Two Sheets in Excel
• How to Use VLOOKUP to Search Text in Excel (4 Ideal Examples)
• How to Use VLOOKUP for Multiple Columns in Excel (6 Examples)
• Use Excel VBA VLOOKUP to Find Values in Another Worksheet
• How to Use VLOOKUP Function with Exact Match in Excel
• VLOOKUP Not Working (8 Reasons & Solutions)