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.
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.
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. 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.
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 and D4 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.
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
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
And press the F9 key
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.
We can change the lookup values to see the change in the result.
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.
This time we have set the two values within the CONCAT function.
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).
Again, we can change the lookup values and will find an updated result at the Team field.
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
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.
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.
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.
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
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
And press F9, you will get the insights
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.
Change the lookup values to observe the changes in the result.
Note that to concatenate the lookup values, you can use the CONCAT or TEXTJOIN function instead of ampersand.
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.