Multiple Criteria Lookup With Index And Match
We can expand on the Index and Match formula and use an array formula to look up a value based on multiple criteria. This is an array formula so need to press ‘Ctrl, Shift, Enter’ to put curly brackets around the formula.
Example 1:
In the table below, we can use a first and last name as criteria to find the Country.
First Name value to lookup: Max
Last Name value to lookup: Bradley
Result: Ireland
Formula:
{=INDEX(C2:C9,MATCH("Max"&"Bradley",(A2:A9)&(B2:B9),0),1)}
~ | A | B | C |
---|---|---|---|
1 | First Name | Last Name | Country |
2 | Joe | Bloggs | Australia |
3 | Jane | Smith | Venezuela |
4 | Ken | Jones | Spain |
5 | Kylie | Moore | Australia |
6 | Nicole | Jennings | England |
7 | Adam | Taylor | England |
8 | Anthony | McDonald | Australia |
9 | Max | Bradley | Ireland |
Example 2:
Let’s say we now have 2 Max Bradleys, one from Australia and one from Ireland. A 4th column “Rank” is introduced, to get the rank of max Bradley from Australia we can follow the above syntax and just add more paramaters in the criteria and range sections within the MATCH function
First Name value to lookup: Max
Last Name value to lookup: Bradley
Country to lookup: Australia
Result: 7
Formula:
{=INDEX(D2:D9,MATCH("Max"&"Bradley"&"Australia",(A2:A9)&(B2:B9)&(C2:C9),0),1)}
A | B | C | D | |
---|---|---|---|---|
1 | First Name | Last Name | Country | Rank |
2 | Joe | Bloggs | Australia | 1 |
3 | Jane | Smith | Venezuela | 2 |
4 | Ken | Jones | Spain | 3 |
5 | Kylie | Moore | Australia | 4 |
6 | Nicole | Jennings | England | 5 |
7 | Adam | Taylor | England | 6 |
8 | Max | Bradley | Australia | 7 |
9 | Max | Bradley | Ireland | 8 |