Vlookup multiple criteria – 3 ways to check multiple criteria

Can VLOOKUP check multiple criteria to match the value like SUMIFS or COUNTIFS ? The answer may be no, because by default the VLOOKUP function can check only one criteria for matching. But in case if you want to use VLOOKUP to match more than one conditions, this article will explain you the different ways to check Vlookup multiple criteria.

There are three different methods to check vlookup multiple criteria. They are:

  1. Combine multiple criteria in to one with “&” and use this as one criteria
  2. Check multiple criteria in VLOOKUP with CHOOSE array formula
  3. Check multiple criteria in VLOOKUP with IF array formula

Now let us discuss each of the methods one by one.

1. Combine multiple criteria in to one with “&” and use this as one criteria

To check multiple criteria in VLOOKUP, we can combine the multiple criteria in to one value with “&” in both table and lookup value. In order to do that, we will have to add a column in the table. In that additional column you have to merge multiple columns in to one column with “&” as below:

Vlookup multiple criteria

The combined column should be left most column in the table to apply VLOOKUP. In case if it is not the left most column, you can read: Vlookup to the left: 3 ways to lookup left in excel

You can use “&” in VLOOKUP formula for multiple criteria in lookup value. The formula will look like as below.

=VLOOKUP(G6&H6,A:E,4,0)

2. Check multiple criteria in VLOOKUP with CHOOSE array formula

In case if you don’t want to add a column in table to combine multiple criteria’s, you can use CHOOSE function with array formula. This formula will check multiple columns in table and return the results for all matching criteria.

{=VLOOKUP(G11&H11,CHOOSE({1,2},B:B&C:C,D:D),2,0)}

Note: Since this is an array formula, you will have to press Ctrl+Shift+Enter while submitting the formula. In case if you simply press enter, this formula will not give the results. You can observe that the array formula starts and ends with { } but no need to add this manually. After entering formula just press Ctrl+Shift+Enter.

3. Check multiple criteria in VLOOKUP with IF array formula

Similar to CHOOSE function, you can also use IF function with array formula to check multiple criteria in vlookup.

{=VLOOKUP(G16&H16,IF({1,0},B:B&C:C,D:D),2,0)}

Note: Since this is an array formula, you will have to press Ctrl+Shift+Enter while submitting the formula. In case if you simply press enter, this formula will not give the results. You can observe that the array fomula starts and ends with { } .

However, the array formula will take more time to execute, therefore it is not advisable to use array formula when you are working with large data.

You can download the file with examples to check vlookup multiple criteria in below link.

Leave a Comment