Shakil Ahmed
In Excel, you may need to search for values using different lookup functions like- the LOOKUP, VLOOKUP, and HLOOKUP Functions. Today I am going to show you how to use the VLOOKUP function with 2 conditions in Excel. For this session, I am using Microsoft 365, you can use your preferred version.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
3 Easy Methods to Use VLOOKUP Function with 2 Conditions in Excel
In this article, you will see three easy methods to use the VLOOKUP function with 2 conditions in Excel. In the first method, you will see the use of a helper column to apply the function formula. Again, for the second method, you will witness the employment of a helper function instead of a helper column. And lastly, I will combine the VLOOKUP and IF functions for achieving the above-described purpose.
I will use the following sample data set for describing this article.
1. Use Helper Column
In the first method, I will use a helper column to join data from two different columns and perform the lookup based on the helper column data. The steps for this procedure are as follows.
Steps:
- First of all, make a helper column on the left-most side of your primary data set as the VLOOKUP function will look for a value in the first column.
- Secondly, insert the following formula in cell B5 to join the values of cells C5 and D5.
=C5&D5
- Thirdly, press Enter and use AutoFill to see the result for that whole column.
- Afterward, type the following formula of the VLOOKUP function in cell C23, where you will see the salary of a person in a specific department.
=VLOOKUP(C21&C22,B4:E19,4,0)- Here C21 and C22 are the two criteria, and we have concatenated them while providing them within VLOOKUP.
- B4:E19 is the lookup range and 4 is the search column since the desired value is in the 4th column.
- Lastly, the 0 in the formula is for an exact match.
- Finally, press Enter and you will get the salary of Joseph who works in the IT department.
Read More: Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
Similar Readings
- VLOOKUP Not Working (8 Reasons & Solutions)
- Excel LOOKUP vs VLOOKUP: With 3 Examples
- Excel VLOOKUP to Find Last Value in Column (with Alternatives)
- Excel VLOOKUP to Return Multiple Values Vertically
- VLOOKUP with Numbers in Excel (4 Examples)
2. Employ 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. Here, I will use the CHOOSE function in the formula as the helper function. For a better understanding, go through the following steps.
Steps:
- Firstly, in cell D23, use the following formula, where I have used the CHOOSE function as a helper function inside the formula of the VLOOKUP function.
=VLOOKUP(C21&C22,CHOOSE({1,2},$B$5:$B$19&$C$5:$C$19,$D$5:$D$19),2,0)
Formula Breakdown
=VLOOKUP(C21&C22,CHOOSE({1,2},$B$5:$B$19&$C$5:$C$19,$D$5:$D$19),2,0)
- The CHOOSE function portion of this formula works as a virtual helper table. I have used 1 and 2 (within the curly braces) as the index number.
- Then, I concatenated the Employee Name and Department columns together, this will be the first column of my virtual table.
- Here I have inserted the Salary column in the value 2 field and this will be the second column of the virtual table.
- After that, hit Enter and get the desired result by applying the formula to two conditions.
Read More: How to VLOOKUP with Multiple Conditions in Excel (2 Methods)
3. Combine VLOOKUP and IF Functions
There is another method to use the VLOOKUP function with 2 conditions in Excel. For that, you have to use the IF function here to define the lookup array for the VLOOKUP function. You can see the details of this procedure in the following.
Steps:
- In the beginning, in cell C23 insert the following formula where the IF function will work as the lookup array.
=VLOOKUP(C21, IF(C5:C19=C22, B5:D19, ""), 3, FALSE)
Formula Breakdown
=VLOOKUP(C21, IF(C5:C19=C22, B5:D19, “”), 3, FALSE)
- Firstly, the IF function will search for the value of cell C21 in the cell range C5:C19.
- Then, if it finds any match it will show the match with its corresponding rows from columns B and C as well.
- Lastly, the VLOOKUP function will search for an exact match of the value of cell C21 from the table array and it will look for the value in the third column of the array.
- Finally, press Enter to see the outcome of the formula and the salary of the person of cell C21 who works in the department mentioned in cell C22.
Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)
Things to Remember
- Remember to give the correct lookup range and col index number reference in the formula. Otherwise, you will not get your desired result.
- If you enter a value less than 1 as the column index number, the function will return an error.
- If you are not using Microsoft Office 365 then you have to press Ctrl + Shift + Enter instead of only Enter to get the result from an array formula.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to use the VLOOKUP function with 2 conditions in Excel. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.
Further Readings
- How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)
- VLOOKUP SUM Multiple Rows (4 Ways with Alternative)
- VLOOKUP to Search Text in Excel (4 Easy Ways)
- 10 Best Practices with VLOOKUP in Excel
- VLOOKUP with Two Lookup Values in Excel (2 Approaches)
- Combining SUMPRODUCT and VLOOKUP in Excel (2 Examples)
