VLOOKUP with 2 Conditions

In Excel, you may need to search values using different lookup functions: LOOKUP, VLOOKUP, HLOOKUP. Today we are going to show you how to use VLOOKUP with 2 conditions or criteria. For this session, we are using Excel 2019, you can use your preferred version.

First things first, let’s get to know about today’s practice workbook.

Dataset - VLOOKUP with 2 Conditions

Here we have a dataset of few employees with their respective departments and salary. Using this dataset, we will search for value utilizing VLOOKUP with a couple of criteria.

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

Practice Workbook

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

2 Conditions VLOOKUP

1. Using a Helper Column

In this section, we are going to see how to VLOOKUP with 2 conditions using a helper column. To know about the function, visit this VLOOKUP article.

For our example, we will set Employee and Department as our criteria. And derive the Salary from these criteria values.

Criteria set - VLOOKUP with 2 Conditions

We need to provide the values within the Employee and Department criteria and find the Salary from these values.

Criteria Value Set - VLOOKUP with 2 Conditions

First of all, we need to introduce a helper column.

Helper column - VLOOKUP with 2 Conditions

We will fill the column by concatenating values from Employee Name and Department (here our criteria are based on these two columns, so we concatenate these two).

To concatenate we need to use & between the Cell Reference of two values.

=C4&D4   

C4 and D4 are the Cell Reference of the first row from the Employee Name and Department.  

Concatenate - VLOOKUP with 2 Conditions

This will join the two values together.

Concatenate result - VLOOKUP with 2 Conditions

Now fill up the entire Helper column.

Values in helper column - VLOOKUP with 2 Conditions

We will use the VLOOKUP function for our formula and the formula will be

=VLOOKUP(H3&H4,B3:E18,4,0)

VLOOKUP formula - VLOOKUP with 2 Conditions

Here H3 and H4 are the two criteria, and we have concatenated while providing within VLOOKUP.

B3:E18 is the lookup range and 4 is the search column since our desired value is in the 4th column.

0 for the Exact Match.

VLOOKUP formula result - VLOOKUP with 2 Conditions

Here we have found the salary for the employee Jack from the IT department. You can change the criteria value, the result will be updated.

Change in criteria value - VLOOKUP with 2 Conditions

Here we have set the employee name as Joshep and found his salary.

2. Using a Helper Function

If you don’t want to use a helper column then you may need to use a function along with the VLOOKUP function.

Criteria Value Set - VLOOKUP with 2 Conditions

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.

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

Now let’s write the function to search the value using two criteria within VLOOKUP. And hope you have already understood that the formula will be a combination of VLOOKUP and CHOOSE.

The formula will be

 =VLOOKUP(G3&G4,CHOOSE({1,2},$B$4:$B$18&$C$4:$C$18,$D$4:$D$18),2,0)

VLOOKUP - CHOOSE formula - VLOOKUP with 2 Conditions

It is an array formula, so we need to press CTRL + SHIFT + ENTER to execute it perfectly.

VLOOKUP-CHOOSE formula result - VLOOKUP with 2 Conditions

Here we found the salary for the employee from the department we have provided.

You may wonder how this formula works! Let’s help you a bit.

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 Employee Name and Department column together, this will be the first column of our virtual table.

Here we have inserted the Salary column in the value 2 field and this will be the second column of the virtual table.

To visualize, select the CHOOSE portion

CHOOSE insights - VLOOKUP with 2 Conditions

And press F9, you will get the insights

CHOOSE insights result - VLOOKUP with 2 Conditions

Here we have two columns that’s why used 2 at the column number in the VLOOKUP function.

Change the criteria values and you will get the updated salary.

Change value - VLOOKUP with 2 Conditions

A Versatile Alternative

You can use an alternative approach to lookup values with multiple conditions. For that, we need to use the combination of INDEX and MATCH.

To know about these functions visit these articles; INDEX, MATCH.

Our formula will be the following one

=INDEX($D$4:$D$18,MATCH(1,($B$4:$B$18=G3)*($C$4:$C$18=G4),0))

INDEX - MATCH formula - VLOOKUP with 2 Conditions

Inside INDEX we have set $D$4:$D$18 as the array range and the MATCH function sets the row number for the search.

Within MATCH we have set 1 as the lookup_value, then checked the criteria with its respective column and multiplied. This multiplication resultant array is the lookup_array for the function.

This is an array formula, so don’t forget to press CTRL + SHIFT + ENTER to execute the formula.

INDEX - MATCH formula result - VLOOKUP with 2 Conditions

Conclusion

That’s all for today. We have shown how to use VLOOKUP with 2 conditions. 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