INDEX MATCH with multiple criteria enables you to look up and return values even if there are no unique values to look for.
This is not achievable with any other lookup formula without inserting helper columns.
In this guide, you learn the three steps to make it work every timeđ
Grab the workbook here so you can tag along, and letâs set the scene with an example below.
Table of Contents
Example of using INDEX MATCH with multiple criteria
Step 1: Insert a normal INDEX MATCH formula
Step 2: Change the MATCH lookup value to 1
Step 3: Write the criteria
Example of using INDEX MATCH with Multiple Criteria
So, you got this employee database.
You want to make the database easier to search, so youâre creating a small tool (to the right in the worksheet).
In that tool, anyone should be able to type in the name and division of an employee and it will find that personâs salary (and show it in cell G4).
âThatâs easy, I can just use VLOOKUP.â
Wait a minuteâ
Unfortunately, itâs not that easy.
You see, the problem is that there are actually two employees called âSteve Jonesâ. That means the lookup value has two matches in the lookup column (column a).
Thereâs nothing unique about Steve Jonesâ name on its own.
In Microsoft Excel terminology, âNameâ would be one criterion.
So, one criterion didnât cut it.
But if you include another criterion, like âDivisionâ, you make Steve Jones unique.
Now, while âSteve Jonesâ appears several times on the list, thereâs only one âSteve Jones from the sales divisionâ.
This is the kind of magic you can do with INDEX MATCH with multiple criteria.
Now, follow the 3 steps below to learn how this wonderful formula works đ
Step 1: Insert a normal INDEX MATCH formula
INDEX MATCH with multiple criteria is an array formula created from the INDEX and MATCH functions.
An array formula has a syntax that is different from normal Excel formulas. Itâs basically a normal formula on steroidsđȘ
Kasper Langmann, co-founder of Spreadsheeto
The synergies between the INDEX and MATCH functions are that:
- MATCH searches for a value and returns a location
- MATCH feeds the location to the INDEX function
- Then INDEX transforms this location into a result
Start the formula
First, start the MATCH function, and then put the INDEX function around the MATCH function to complete the formula.
Start with:
Click to copy
Step 1.1) As the first argument in the MATCH function, enter the lookup_value. This is what you are looking for.
In this case, youâre looking for an employee with the name âSteve Jonesâ.
Select (or manually enter) cell G2 as the lookup_value, then separate with a comma to move on to the lookup_array.
Step 1.2) The lookup_array is the column where the MATCH function looks for the lookup value.
Select the column with the names, and then enter a comma to move on to the [match_type].
Now your formula should look like this:
Click to copy
Step 1.3) A little drop-down list appears that gives you the choice between 1, 0, and -1.
The 0 option is the Exact match option and is most commonly used.
The -1 and 1 are similar to VLOOKUPâs âapproximate matchâ method.
Write 0 or double-click the 0 â Exact match option in the drop-down menu and type the end parenthesis.
Your formula should now look like this:
Click to copy
Step 1.4) Wrap the INDEX function around the MATCH function.
Your formula should look like this by now:
Click to copy
But weâre not done yetâ
The syntax of the INDEX function goes:
INDEX(array, row_num, [column num])
The MATCH function should be the second argument in the INDEX syntax.
Right now, itâs the first argument.
So, begin writing the real first argument: the array.
The INDEX array is the column you want to return values from.
The purpose of the multiple criteria INDEX MATCH is to find the salary of a specific employee.
So, the array is the salary column (column D).
Your formula should look like this by now:
Click to copy
Just put an extra parenthesis at the end to wrap up the INDEX function.
So, the final formula looks like this:
Click to copy
EXPLANATION
The MATCH function searches for the value in G2 (âSteve Jonesâ) in the database and then returns a number.
This number is the row in the dataset where the name âSteve Jonesâ is found.
This row number is then fed into the syntax of the INDEX function.
Problem: Thereâs more than one employee called âSteve Jonesâ. Whose salary are we actually seeing?
Excel lookup formulas always search from top to bottom, so youâre seeing the salary of the top Steve Jones (in row 3).
Thatâs the Steve youâre looking for.
But itâs dumb luck that you found himđ
To make sure we always find Steve Jones from sales, follow the rest of the steps below.
Kasper Langmann, co-founder of Spreadsheeto
Step 2: Change the lookup value to 1
Now, you need to change your normal INDEX MATCH formula into an array formula.
Sounds hard?
Donât worry, Iâll guide you step-by-stepđ
Step 2.1) Change the lookup_value of the MATCH function to 1.
So, the formula changes from:
Click to copy
To:
Click to copy
The âtheoryâ behind this is not as simple as changing the lookup_value.
Since youâre changing the formula from a normal one to an array formula, the structure of the formula changes a bit as well.
By changing the lookup_value to 1, youâre not actually telling the MATCH function to search for the number 1 in the lookup_array (name column).
In Excel-language, 1 means TRUE. 0 means FALSE.
Kasper Langmann, co-founder of Spreadsheeto
When you enter our two criteria in the next step, the 1 in the MATCH function simply means:
âLook through the rows in the data and return the row number where all of the criteria are TRUEâ.
If you wrote a zero, the formula would look for a row where all of our criteria are FALSE â and that wouldnât really make sense.
Step 3: Write the criteria
The criteria replace the second argument of the MATCH function, with this structure:
(range=criterion1)*(range=criterion2)*(range=criterion3)*âŠ
This way, you can have as many criteria as you need.
INDEX MATCH function with two criteria
Itâs typically enough to use two criteria to make your lookup value unique.
Criterion 1 = name
Criterion 2 = division
Letâs see if you can find âSteve Jones from salesâ or if heâs lost in the woodsđł
Step 3.1) Replace this structure:
(range=criterion1)*(range=criterion2)
With the actual criteria:
(A:A=G2)*(B:B=G3)
- A:A is the column with names.
- G2 is the name youâre looking for.
- B:B is the column with division.
- G3 is the division youâre looking for.
Step 3.2) Write that as the second argument in the MATCH function, replacing whatâs currently there.
Your INDEX MATCH formula should now look like this:
Click to copy
It seems weird typing random parenthesisâ into formulas. But this is how you structure the criteria, so the array formula understands it.
Kasper Langmann, co-founder of Spreadsheeto
If you want to go with 3 criteria, you add the 3rd criterion to the criteria structure:
(range=criterion1)*(range=criterion2)*(range=criterion3)
Converted to actual ranges and criteria, it would look like this:
(A:A=G2)*(B:B=G3)*(C:C=G4)
Step 3.3) If youâre using Microsoft 365 just press Enter and watch your beautiful multiple criteria lookupđĄ
If youâre not using Microsoft 365, donotpress Enter when youâre done with the formula. It wonât work. Instead, press Ctrl + Shift + Enter.
Explanation: Ctrl + Shift + Enter
If you donât have Microsoft 365, array formulas need to be handled differently than other formulas.
So, instead of pressing Enter, press Ctrl + Shift + Enter when you are done with the formula.
Warning: {curly brackets} will appear around your formula. They are supposed to be there!
Every time you make changes to this formula, you must end with Ctrl + Shift + Enter
(Instead of just regular âEnterâ as you are probably used to)
Conclusion
Wow⊠You just learned how to use INDEX MATCH with multiple criteriaâŠ
It wasnât so scary after all, right? đ
Now, youâve created a tool to easily look up employees and return their salary â even if there are multiple employees with the exact same name!
All are fueled by the INDEX and MATCH functions.
You may also be interested in
My brand-new video on INDEX MATCH with Multiple Criteria (make sure to like and subscribe!)
Excel INDEX MATCH is not the only lookup formula out there (although itâs the only one you can turn into an array function).
If you want to expand your toolbox further, definitely get to know the VLOOKUP function, HLOOKUP, and the new XLOOKUP.
If you thought this guide was hard to follow, you should dive into the INDEXand the MATCH functions separately.
Besides, thereâs another, underrated, way to achieve a lookup with multiple criteria. Thatâs the good old Excel filter.
Finally, you might also enjoy my pivot table tutorial or my favorite collection of project management templates.
Author
Written by Kasper Langmann, co-founder of Spreadsheeto and a certified Microsoft Office Specialist.
This tutorial reflects over 50 hours of dedicated research and writing, based on my 10+ years of professional Microsoft Excel experience.
Last updated on February 8th, 2024.
One last thing before you go: make sure to sign up for my free Microsoft Excel course!