Asikul Himel
When you have to deal with massive data from multiple worksheets across your Excel workbook, The VLOOKUP Function is your savior. The VLOOKUP function is one of the most useful and at the same time one of the most sophisticated functions in Excel. The VLOOKUP is also very effective when you have multiple conditions to face with. Today in this article, we will discuss some methods to do VLOOKUP with Multiple Conditions.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
2 Suitable Ways to Use VLOOKUP with Multiple Conditions in Excel
Before beginning, consider a situation where you are given the Name, Region, and Salary of some sales representatives. Here, we need to find their salaries according to their Region. We will use The VLOOKUP Function with multiple conditions to complete that task.
1. Using Helper Column
The helper column makes it easy to understand what’s going on in the worksheet. Moreover, a helper column makes it faster as compared with the array function. Here, we will use a helper column to VLOOKUP with multiple conditions in excel. So to learn more, follow the steps below!
Steps:
- First of all, create another data table where we will find out the salaries of Jon, Ben, and Tony with respect to their regions.
- Now, insert a Helper Column between the columns Region and Salary.
- After that, in cell D5 of the Helper column, apply this formula:
=B5&"|"&C5
🗝️How Does the Formula Work?
Using this formula we will join the cells of the Region and Name by using the Ampersand Operator (&). Moreover, we have used the separator(|) to separate those two different words so that they may never end up giving the same result when combined.
- After that, press Enter to get the result.
- Now, apply the same formula to the rest of the cells using the Fill Handle.
- After that, we will apply the VLOOKUP function in cell H5. Here, write down the following formula.
=VLOOKUP($G5&"|"&H$4,$D$5:$E$13,2,FALSE)🗝️ How Does the Formula Work?
- Here the lookup_value is $G5&”|”&H$4. Where G5 is the name of the representative and H4 is his corresponding region. Use absolute cell reference ($) to block the rows and columns.
- Lookup_array is $D$5:$E$13
- Col_index_num is 2 and we want the EXACT match (FALSE)
- Now, get the result by pressing Enter key.
- Finally, use the Fill-Handle to get the result for all sales representatives.
That’s how you can solve your problem using the Helper Column.
Read More: VLOOKUP with 2 Conditions in Excel (2 or more Ways)
Similar Readings
- VLOOKUP Not Working (8 Reasons & Solutions)
- Excel LOOKUP vs VLOOKUP: With 3 Examples
- What Is a Table Array in VLOOKUP? (Explained with Examples)
- INDEX MATCH vs VLOOKUP Function (9 Examples)
- Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
2. Combining Excel CHOOSE and VLOOKUP Functions with Multiple Conditions
Another way to VLOOKUP with multiple conditions is to use the array formula. We can apply the CHOOSE function combined with the VLOOKUP function to solve our problem. The procedures are described below.
Steps:
- First, in cell G5, write down the following formula.
=VLOOKUP($F5&"|"&G$4,CHOOSE({1,2},$B$5:$B$13&"|"&$C$5:$C$13,$D$5:$D$13),2,FALSE)
🗝️ How Does the Formula Work?
- Here, the lookup_value is $F5&”|”&G$4.
- Lookup_array is CHOOSE({1,2},$B$5:$B$13&”|”&$C$5:$C$13,$D$5:$D$13). Here we used the CHOOSE function as the Virtual Helper Column.
- In the CHOOSE function, index_num is {1,2} so that we can pick our values from Value1 or Value2
- Value1 is $B$5:$B$13&”|”&$C$5:$C$13 and value2 is $D$5:$D$13.
- Col_index_num is 2 and we want the EXACT match (FALSE)
- Since this formula is an Array Formula, get the result by pressing “CTRL+SHIFT+ENTER”. But in the new version of Microsoft Excel, pressing the Enter key is enough.
- As our formula is working perfectly, apply this formula to the rest of the cells to get the final result by using the Fill Handle.
Things to Remember
- The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function Never searches for the data on the left.
- If you enter a value less than 1 as the column index number, the function will return an error.
- To lock the array while choosing your Table Array, utilize absolute cell references ($).
- As the combination of the VLOOKUP and the CHOOSE function is an Array formula you have to press SHIFT+CTRL+ENTER to apply the formula if your Excel is an older version.
Conclusion
We have discussed how to VLOOKUP with multiple conditions using two different approaches. Though this function is slightly difficult for new users to comprehend, we tried to make it as simple as possible. Hope this article is useful for you. Share your thoughts if you have any confusion.
Similar Articles to Explore
- Combine SUMIF and VLOOKUP in Excel (3 Quick Approaches)
- VLOOKUP to Return Multiple Columns in Excel (4 Examples)
- Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel
- How to Use VLOOKUP for Rows in Excel (With Alternatives)
- VLOOKUP with Multiple Criteria in Excel (6 Examples)
- How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)
- VLOOKUP Formula in Excel with Multiple Sheets (4 Simple Tips)
