Depending on circumstances you may need to use multiple lookup values with VLOOKUP. To ease 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 more 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 of the two lookup values corresponding to the data table. And the approach for that can be like the one below-
=C5&D5
- 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.
Read More: 10 Best Practices with VLOOKUP in Excel
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-
=VLOOKUP(H4&H5,B5:E14,4,0)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 in the very first column of this lookup_array.
- Next, press the ENTER key from the keyboard.
- Within a moment, we have successfully extracted our output with two lookup values using the VLOOKUP.
Read More: 7 Practical Examples of VLOOKUP Function 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.
Steps:
- First, select a cell (H6) and write the below formula down-
=VLOOKUP(CONCAT(H4,H5),B5:E14,4,0)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.
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-
=VLOOKUP(TEXTJOIN(,TRUE,H4,H5),B5:E14,4,0)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.
- If we change the inputs from the “League” and “Status” sections the output will be changed according to the given conditions.
Read More: How to Apply Double VLOOKUP 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-
=VLOOKUP(G4&G5,CHOOSE({1,2},$B$5:$B$14&$C$5:$C$14,$D$5:$D$14),2,0)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 concatenated the League and Status columns 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 to get the final output.
3. Use of MATCH Function with VLOOKUP for Two Lookup Values in Excel
In this method, we will use the MATCH function along with the VLOOKUP function to find the Champion team or the Runner Up team of any league.
Steps:
- First, select cell G6 to enter the formula.
=VLOOKUP(G4,B6:D10,MATCH(G5,B5:D5,0),FALSE)- Here, the only lookup values are in Column B as League and Row C6: D10 as the name of the Champion team and the Runner Up team.
- Here, G4 is the first lookup value and G5 is the second lookup value.
- Next, click ENTER.
- Here, the final output is the Team name that matches the condition of those two lookup values.
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-
=INDEX(D5:D14, MATCH(1, (G4=B5:B14) * (G5=C5:C14), 0))Where,
- The INDEX-MATCH function works as a two-way lookup. Thus the MATCH function will look for values 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”.
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.
Related Articles
- How to Use VLOOKUP to Find Duplicates in Two Columns
- How to Use VLOOKUP Function with Exact Match in Excel
- How to Find Second Match with VLOOKUP in Excel
- VLOOKUP and Return All Matches in Excel
- VLOOKUP Fuzzy Match in Excel
- Excel VLOOKUP to Find Last Value in Column
- How to Use VLOOKUP to Search Text in Excel
- How to Apply VLOOKUP by Date in Excel
- Return the Highest Value Using VLOOKUP Function in Excel
- VLOOKUP with Numbers in Excel
<< Go Back to VLOOKUP Multiple Values | Excel VLOOKUP Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Fascinating article. Very inventive combination of functions. One problem, option 3 does not work as posted. If you run the function it will return the first entry that it finds rather than whichever one you are trying to capture. There is no way to indicate whether you want the champion or the runner-up.
Hello Rick Howard, Thank you for your query. Therefore, method 3 is updated in the article. Now you may find any champion team or any runner Up team of any random League using The VLOOKUP function and the MATCH function.