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.
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.
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.
We need to provide the values within the Employee and Department criteria and find the Salary from these values.
First of all, we need to introduce a helper column.
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 and D4 are the Cell Reference of the first row from the Employee Name and Department.
This will join the two values together.
Now fill up the entire Helper column.
We will use the VLOOKUP function for our formula and the formula will be
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.
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.
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.
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
It is an array formula, so we need to press CTRL + SHIFT + ENTER to execute it perfectly.
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
And press F9, you will get the insights
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.
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.
Our formula will be the following one
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.
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.