# How to Use VLOOKUP with Multiple Conditions in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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

### 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 #### Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  