# 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”:

Name | Date | Amount |
---|---|---|

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 `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:

Borrower | Date Lent | Amount |
---|---|---|

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 `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!

## Comments

No comments have yet been submitted. Be the first!