How to VLOOKUP with Multiple Conditions in Excel (2 Methods)

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 VLOOKUP with Multiple Conditions in Excel

Consider a situation where you are given the Name, Region, and Salary of some sales representatives. We need to find their salaries according to their Region. We will use The VLOOKUP Function with multiple conditions to complete that task.

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel


1. Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

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. Follow these steps below!

Step 1:

  • Create another data table where we will find out the salaries of Jon, Ben, Tony with respect to their regions.

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

Step 2:

  • Insert a Helper Column between the columns Region and Salary

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • In cell D4 of the Helper column, apply this formula:
=B4&"|"&C4
  • Using this formula we will join the cells of the Region and Name We have used the separator to separate those two different words so that they may never end up giving the same result when combined.

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • Press Enter to get the result.

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • Now apply the same formula to the rest of the cells.

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

Step 3:

  • Apply the VLOOKUP function in cell H4. Insert all the values into the function and the final form of the formula is:
=VLOOKUP($G4&"|"&H$3,$D$4:$E$12,2,FALSE)
  • Here the lookup_value is $G4&”|”&H$3. Where G4 is the name of the representative and H3 is his corresponding region. Use absolute cell reference ($) to block the rows and columns.
  • Lookup_array is $D$4:$E$12
  • Col_index_num is 2 and we want the EXACT match (FALSE)

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • Get the result by pressing Enter

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

  • Copy and drag the formula cell to the end of the table to get the result for all sales representatives. That’s how you can solve your problem using the Helper Column.

Insert a Helper Column to VLOOKUP with Multiple Conditions in Excel

Read More: VLOOKUP with 2 Conditions in Excel (2 or more Ways)


Similar Readings


2. Apply the CHOOSE Function to VLOOKUP with Multiple Conditions in Excel

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.

Step 1:

  • In cell H4, apply the VLOOKUP with the CHOOSE After inserting all the values, the final formula is:
=VLOOKUP($F4&"|"&G$3,CHOOSE({1,2},$B$4:$B$12&"|"&$C$4:$C$12,$D$4:$D$12),2,FALSE)
  • Where the lookup_value is $F4&”|”&G$3.
  • Lookup_array is CHOOSE({1,2},$B$4:$B$12&”|”&$C$4:$C$12,$D$4:$D$12). 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$4:$B$12&”|”&$C$4:$C$12 and value2 is $D$4:$D$12.
  • Col_index_num is 2 and we want the EXACT match (FALSE)

Apply the CHOOSE Function to VLOOKUP with Multiple Conditions in Excel

  • Since this formula is an Array Formula, get the result by pressing “CTRL+SHIFT+ENTER”.

Apply the CHOOSE Function to VLOOKUP with Multiple Conditions in Excel

Step 2:

  • As our formula is working perfectly, apply this formula to the rest of the cells to get the final result

Apply the 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.

👉 When you select your Table_Array you have to use the absolute cell references ($) to block the array.

👉 Since the combination of the VLOOKUP and the INDIRECT function is an Array formula you have to press SHIFT+CTRL+ENTER to apply the formula.


Conclusion

We have discussed how to VLOOKUP with multiple conditions using two different approaches. Though this function is slightly difficult for the 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