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.

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

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&D4`

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

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

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.

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

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

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

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

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

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

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

**10 Best Practices with VLOOKUP in Excel****VLOOKUP with Drop Down List in Excel****VLOOKUP Fuzzy Match in Excel (3 Quick Ways)****How to Use VLOOKUP with COUNTIF (3 Ways)****How to Use VLOOKUP for Multiple Columns in Excel****Combining SUMPRODUCT and VLOOKUP in Excel (2 Examples)****Using VLOOKUP with IF Condition in Excel (5 Real-Life Examples)**