Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VLOOKUP with Multiple Conditions in Excel

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.

Insertion of a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • Now, insert a Helper Column between the columns Region and Salary.

Insertion of a Helper Column to VLOOKUP with Multiple Conditions in Excel

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

Insertion of a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • 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)

Insertion of a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • Now, get the result by pressing Enter key.

  • Finally, use the Fill-Handle to get the result for all sales representatives.

Insertion of a Helper Column to VLOOKUP with Multiple Conditions in Excel

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


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)

Application of CHOOSE Function to VLOOKUP with Multiple Conditions in Excel

🗝️ 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.

Application of CHOOSE Function to VLOOKUP with Multiple Conditions in Excel

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

Application of CHOOSE Function to VLOOKUP with Multiple Conditions in Excel


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
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo