Excel find rows that match criteria

How to Use INDEX MATCH With Multiple Criteria in Excel

INDEX MATCH with multiple criteria enables you to do a successful lookup when there are multiple lookup value matches.

In other words, you can 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😲

Follow these 3 easy steps to create your very own INDEX MATCH with multiple criteria in a few minutes.

If you want to tag along, download the sample Excel file here.

INDEX MATCH with multiple criteria example

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 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 2 employees called “Steve Jones”. That means the lookup value has 2 matches in the lookup column.

There’s nothing unique about Steve Jones’ name on its own.

In Excel terminology, ‘Name’ would be 1 criteria.

So, 1 criteria didn’t cut it.

But if you include another criteria, 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.

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 formulas. It’s basically a normal formula on steroids💪

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

=MATCH(

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 lookup value, then separate with a comma to move on to the lookup array.

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:

=MATCH(G2,A:A,

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:

=MATCH(G2,A:A,0)

4. Wrap the INDEX function around the MATCH function.

Your formula should look like this by now:

=INDEX(MATCH(G2,A:A,0)

But we’re not done yet✋

The syntax of the INDEX function goes:

INDEX(array, row number, column number)

The MATCH function should be the 2nd argument in the INDEX syntax.

Right now, it’s the 1st argument.

So, begin writing the real 1st 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:

=INDEX(D:D,MATCH(G2,A:A,0)

And just put an extra parenthesis at the end to wrap up the INDEX function.

So, the final formula looks like this:

=INDEX(D:D,MATCH(G2,A:A,0))

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

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😊

1. Change the lookup value of the MATCH function to 1. It’s just as easy as it sounds.

So, the formula changes from:

=INDEX(D:D,MATCH(G2,A:A,0))

To:

=INDEX(D:D,MATCH(1,A:A,0))

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 (last name column).

In Excel-language, 1 means TRUE. 0 means FALSE.

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 replaces the 2nd argument of the MATCH function, with this structure:

(range=criteria1)*(range=criteria2)*(range=criteria3)*…

This way, you can have as many criteria as you need.

INDEX MATCH with 2 criteria

It’s typically enough to use 2 criteria to make your lookup value unique.

Criteria 1 = name

Criteria 2 = division

Let’s see if you can find “Steve Jones from sales” or if he’s lost in the woods🌳

Replace the structure above with the actual criteria:

(range=criteria1)*(range=criteria2)

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

Write that as the 2nd argument in the MATCH function, replacing what’s currently there.

Your formula should now look like this:

=INDEX(D:D,MATCH(1,(A:A=G2)*(B:B=G3),0))

It seems weird typing random parenthesis’ into formulas, but this is how you structure the criteria, so the array formula understands it.

If you’re using Microsoft 365 just press Enter and watch your beautiful multiple criteria lookup💡

Explanation: Ctrl + Shift + Enter

If you’re not using Microsoft 365, do not press Enter when you’re done with the formula. It won’t work.

Instead, press and hold Ctrl and Shift and then press Enter.

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)

That’s it – Now what?

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 fueled by the INDEX and MATCH functions.

Lookup formulas (and functions) in general are extremely useful and significantly speed up your work🏃

But multiple criteria lookups really save your day when the lookup value is not unique.

But if you really want to work more productively in Excel, you need to dive into macros.

Macros automate work processes, so you can do 50 things with just one click.

I promise you, it’s not as hard as you think.

Join my free 30-minute video course and get started with macros (for beginners).

Other relevant resources

INDEX MATCH is not the only lookup formula out there, although it’s the only one you can turn into an array formula. If you want to expand your toolbox you should definitely get to know VLOOKUP and the new XLOOKUP.

If you thought this was hard to understand, you should dive into the INDEX function and the MATCH function separately and get to know them better.

Another way to achieve a lookup with multiple criteria is with the good old Excel filter.

I hope this helps you!

Take care👋