How to Use INDEX MATCH With Multiple Criteria in Excel (2024)

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.

How to Use INDEX MATCH With Multiple Criteria in Excel (1)

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).

How to Use INDEX MATCH With Multiple Criteria in Excel (2)

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.

How to Use INDEX MATCH With Multiple Criteria in Excel (3)

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đŸ’Ș

How to Use INDEX MATCH With Multiple Criteria in Excel (4)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.

How to Use INDEX MATCH With Multiple Criteria in Excel (6)

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].

How to Use INDEX MATCH With Multiple Criteria in Excel (7)

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.

How to Use INDEX MATCH With Multiple Criteria in Excel (8)

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.

How to Use INDEX MATCH With Multiple Criteria in Excel (9)

Your formula should now look like this:

Click to copy

Step 1.4) Wrap the INDEX function around the MATCH function.

How to Use INDEX MATCH With Multiple Criteria in Excel (10)

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).

How to Use INDEX MATCH With Multiple Criteria in Excel (11)

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.

How to Use INDEX MATCH With Multiple Criteria in Excel (12)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.

How to Use INDEX MATCH With Multiple Criteria in Excel (13)

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.

How to Use INDEX MATCH With Multiple Criteria in Excel (14)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.

How to Use INDEX MATCH With Multiple Criteria in Excel (15)

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.

How to Use INDEX MATCH With Multiple Criteria in Excel (16)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.

How to Use INDEX MATCH With Multiple Criteria in Excel (17)

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!

How to Use INDEX MATCH With Multiple Criteria in Excel (18)

Kasper Langmann2024-02-15T15:20:23+00:00
How to Use INDEX MATCH With Multiple Criteria in Excel (2024)

References

Top Articles
Latest Posts
Article information

Author: Van Hayes

Last Updated:

Views: 6547

Rating: 4.6 / 5 (66 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Van Hayes

Birthday: 1994-06-07

Address: 2004 Kling Rapid, New Destiny, MT 64658-2367

Phone: +512425013758

Job: National Farming Director

Hobby: Reading, Polo, Genealogy, amateur radio, Scouting, Stand-up comedy, Cryptography

Introduction: My name is Van Hayes, I am a thankful, friendly, smiling, calm, powerful, fine, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.