VLOOKUP in Excel with multiple criteria
An alternative to VLOOKUP in Excel that permits a search based on multiple criteria.
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!
I’ll use the same data set as was used in a previous post where the formatted table is called “Loans”:
|Jane||23 October 2014||$3.25|
|Sarah||30 October 2014||$6.20|
|Jane||4 November 2014||$10.25|
|Jane||20 November 2014||$4.90|
|Lucy||28 November 2014||$1.50|
|Sarah||28 November 2014||$9.45|
If I want to search for the amount of money that was lent to Sarah on 28 November 2014, I cannot use a
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):
The formula utilises an INDEX and MATCH function.
As a means of explaining the components of this formula, I shall replace the variables with square brackets and a letter inside:
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.
As you’d probably be able to guess, as many variables can be added as are required. Simply separate them with a multiplication sign (
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:
|Sarah||28 October 2014||X|
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
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!