VLOOKUP in Excel with multiple criteria

An alternative to VLOOKUP in Excel that permits a search based on multiple criteria.

The VLOOKUP function in Excel is fantastic for searching for information between tables, but one of its limitations is that it can only search against a single criterion.

What happens if more than one criterion needs to be met in order to return the correct value?

The answer is an array formula with a MATCH function nested within an INDEX function. This isn’t as complicated as it looks!

Example Data

I’ll use the same data set as was used in a previous post where the formatted table is called “Loans”:

If I want to search for the amount of money that was lent to Sarah on 28 November 2014, I cannot use a VLOOKUP.

If I used VLOOKUP to search for “Sarah”, it would return a value of $6.20 because the first value for “Sarah” has a date of 30 October 2014 (and VLOOKUP always returns the first value). Likewise, a VLOOKUP search for “28 November 2014” would return a value of $1.50 because Lucy borrowed that amount on that date and her name comes up first.

The solution is to use the following formula, but as an array (which means that Ctrl + Alt + Enter must be pressed to activate it):

=INDEX(Loans,MATCH(1,(Loans[Date]=(DATE(2014,11,28)))*(Loans[Name]="Sarah"),0),3)

The formula utilises an INDEX and MATCH function.

The formula

As a means of explaining the components of this formula, I shall replace the variables with square brackets and a letter inside:

=INDEX([A],MATCH(1,([B]=[C])*([D]=[E]),0),[F])

Part A

This is the array that defines the range of cells to be searched. In this instance, it’s the whole table named “Loans” but it can also be a cell reference.

Parts B and C: Variable 1

This is the first variable to match. Here, I made sure that the date was “28 November 2014”.

Parts D and E: Variable 2

This is the second variable to match. What I did in this case was to check that the name in the Loans table [B] was “Sarah” [C]. I needn’t have used a typed word, as a cell reference is just as valid. Likewise, an equals operator can be substituted for a ‘less than’ or ‘greater than’ but consider that just like a VLOOKUP, this method will also return the first correct answer if there is more than one.

Other variables

As you’d probably be able to guess, as many variables can be added as are required. Simply separate them with a multiplication sign (*).

Part F

This number tells Excel which column within the array (Part A or Loans) that the answer can be found. Since the result (the amount of money lent) is in the third column, the value is “3”.

Adapting the formula

It is possible to use the formula within a table. For instance, let’s say that I created the following formatted table where I want to insert my formula in the cell marked “X” within the “Amount” column:

I can get my formula to reference the cells in the “Name” and “Date” columns to return a value, just as I would with a VLOOKUP:

=INDEX(Loans,MATCH(1,(Loans[Date]=[@Date Lent])*(Loans[Name]=[@Borrower]),0),3)

Conclusion

By nesting a MATCH function within an INDEX function, it’s possible to have all the convenience of a VLOOKUP but with the added advantage of searching against multiple criteria.

Just don’t forget to press Ctrl + Alt + Enter when submitting the formula to tell Excel that this is an array formula!

Have Your Say

Δ