How to Use INDEX+MATCH With Multiple Criteria in 5 Easy Steps

How to Use INDEX+MATCH With
Multiple Criteria in 5 Easy Steps

I was recently contacted by Christian who had a problem with his employee database.

Christian is working in the HR department of a company with more than 50 employees. He asked me:

“Hi, Kasper. I have an employee database and I want to be able to find the salary of an employee. How do I go about doing that?”
Christian Pedersen

Awesome question Christian!

Before deciding which function in Excel to use. We need to take a further look into the data provided.

Instead of going through each row manually (that would be quite boring and increases the risk of making mistakes) we need to use a lookup function.

Excel provides several of these, including the most awesome combination of functions of all time: MATCH + INDEX.

Any lookup function – including a “normal” MATCH INDEX formula – needs to look for a unique piece of information.
In Excel, we call this the lookup value.

The main problem with the database provided: There is no unique piece of information to look for.

Let’s say we use a “normal” INDEX MATCH formula to look up David’s salary. Using a “normal” INDEX MATCH formula we’ll only see the salary of one of the Davids.

That’s a serious problem (and incorrect) as there are 3 employees in the database named “David”.

So how do we find the right David?

We use the MATCH INDEX functions with multiple criteria by following these 5 steps:

Step 1: Understanding the foundation

We are essentially building a tool that can look for an employee and return his or her salary (don’t worry, it sounds harder than it actually is).

This tool needs to be placed somewhere in an Excel sheet.

I recommend creating a defined area where I can select the different criteria and type the formula.

To do this, I simply select range I2:J4 and pick “Thick Outside Border” by clicking the little arrow next to the border button in the “Font” group in the “Home tab”.

After this, I mark where I want my criteria and where the formula goes by writing “Last name” in I2, “Date of birth” in I3 and “Salary” in i4.

Additionally, I hold the left mouse button down on the line between the column names and drag to make them wider, so that the entire content of the cell fits.

Why “Last name” and “Date of birth”?

You might recall that I told you that any lookup function needs to look for a unique piece of information.

Unfortunately, none of the information in the employee database is unique, not even last name or date of birth.

However combining last name AND date of birth greatly increases the chances of finding a unique value.

If we combine “First name” and “Last name” we might end up with a David Jones.

However, if we combine “Last name” and “Date of birth” we would get Jones 07-23-1991. There are two David Jones’ in our data, but only one employee with last name Jones and birthdate 07-23-1991.

So combining last name and date of birth is the smarter choice as this creates a unique identifier in most cases.

“But why even bother combining information in the database?”

ANY look up function needs a unique piece of information to work.

In this case, the database didn’t contain any sort of unique identifier or information.

We, therefore, went ahead and created a unique identifier ourselves by using different criteria, in order to create something unique to look for.

It’s like we tell Excel: “Look for an employee with last name Jones who is born 07-23-1991 and tell me that person’s salary.”

Step 2: Insert a normal MATCH INDEX formula

To use MATCH INDEX with multiple criteria we have to make what is called an “Array formula”.

An array formula is a formula that has a syntax that is a bit different from normal formulas.

We will go about this in 2 steps:

  1. We will insert a normal MATCH INDEX formula
  2. Convert it to an array formula

The MATCH INDEX ‘method’ is a combination of the functions MATCH and INDEX to create a lookup similar to (but better than) VLOOKUP/HLOOKUP.

The synergy between the functions are based on that:

  1. MATCH searches for a value and returns a _location_
  2. MATCH feeds the location to the INDEX function
  3. Then INDEX transforms this location into a result

Here’s how to do it!

Double click on cell J4 to begin the formula.

First we enter the MATCH function, and then we put the INDEX function around the MATCH function to complete the formula.

Begin entering the MATCH function

Start with:

=MATCH(

Then we enter the lookup value, which is what we are looking for.

In this case, we are looking for an employee with a last name equivalent to the one we entered in cell J2. Let’s take “Jones” (for now, we are not going to do anything with ‘Date of Birth’).

Select (or enter manually) cell J2 as lookup value, then separate with a comma to move on to the lookup array.

The lookup array tells Excel where you want the MATCH function to look for the lookup value.

Select the column with the last names, and then enter a comma to move on to the [match_type].

Match types

Now your formula should look like this:

=INDEX(MATCH(J2,B:B,

And now you see a little drop-down menu that give you the choice between 1, 0 and -1.

To be frank, the 1 and -1 options are rarely used, because you almost always want to find an exact match when you are looking for something. Not something that’s exact’ish (that’s actually the point of this entire article…).

So enter 1 manually or double click the ‘0 – Exact match’ option in the drop-down menu.

Exact Match

Your formula should now look like this:

=MATCH(J2,B:B,0)

Now we’ll enter the INDEX function around the MATCH function.

Complete formula

Your formula should look like this by now:

=INDEX(MATCH(J2,B:B,0)

As you can see the syntax of the INDEX function goes:

array, row number, column number.

Right now, the array is highlighted which means that the whole MATCH function we’ve just entered is considered to be the array in the INDEX function. This is not correct, so we’re going to move the MATCH function to the right, by entering the correct array right here:

Array of the Index

The easiest way is to select the entire data range from our employee database and type a comma in the end to move on to the row number.

So we end up with a formula looking like this:

=INDEX(A1:G55,MATCH(J2,B:B,0)

The MATCH function searches for the value in J2 (“Jones”) in the database and then returns a number.

This number is the number of the row in the data where the last name “Jones” is found.

This row number is then fed into the syntax of the INDEX function.

Explanation of function

Put a comma after the right parenthesis of the MATCH function.

Then you can see the INDEX function tooltip change to highlight the [column_num]. What you enter here tells the INDEX function what column in the employee database you want to return data from.

The entire employee database consists of 7 columns (A through G), starting with first name in column A and ending with salary in column G. So chose what you want the result to be.

In Christian’s case he’s looking for the salary of a specific employee, so here I will just type “7” as the column number. If he wanted to know the age of the employee, he could simply type “6” here instead.

Now the formula is done and you can finish with a right parenthesis.

Complete the formula with a right paranthesis

Your formula should look like this:

=INDEX(A1:G55,MATCH(J2,B:B,0),7)

And when you hit enter, you get the salary of an employee whose last name is Jones.

Problem: There’s more than one employee with last name Jones. Whose salary are we actually seeing?

Excel lookup formulas always search from top -> down, so we’re seeing the salary of the top David Jones (in row 27).

If we want to see the salary of any other employee with last name “Jones” we proceed with the following steps…

Step 3: Change the lookup value to 1

Now that we have our normal MATCH INDEX formula ready, we’re prepared to take it to the next level and create a formula that can look up with multiple criteria.

In the following we are going to transform a normal formula to an array formula.

We do this in incremental  and easy steps.

The first step is to change the lookup value of the MATCH function to 1. It’s just as easy as it sounds.

Change lookup value

So the formula changes from:

=INDEX(A1:G55,MATCH(J2,B:B,0),7)

To:

=INDEX(A1:G55,MATCH(1,B:B,0),7)

The “theory” behind this is not as simple as changing the lookup value.

Since we’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, we’re not actually telling the MATCH function to search for the number 1 in the lookup array (last name column).

In “Excel-language” the 1 means TRUE. FALSE equals a 0

When we 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 our criteria are TRUE”.

If we entered a zero, the formula would look for a row where all of our criteria are FALSE – and that wouldn’t really make sense.

Step 4: Enter the criteria

Now it’s time for the criteria. We have 2.

The first criterion is that the last name must be equal to whatever we type in cell J2.

Let’s continue with searching for Jones and see if we can find him or he’ll be lost in the woods.

The second criterion is that the employee’s date of birth must be equal to whatever we type in J3.

Let’s start with the first criterion:

Place the marker after the “B:B” in the lookup array of the MATCH function and type =J2.

After this, you enclose the entire criterion with a parenthesis starting before the B:B and ending after the J2.

Changing the function

Your formula should now look like this:

=INDEX(A1:G55,MATCH(1,(B:B=J2),0),7)

It seems weird typing random parenthesis’ into formulas, but this is how you structure the criterion so the array function can understand it.

To tell the formula that you are entering a criterion you must enter it in this format:

(range=criterion)

When applied to our situation it looks like this:

(B:B=J2)

You can type the criterion in the formula directly. Then it would look like this:

(B:B=”Jones”)

But I highly recommend referencing to the cell (J2), so that you can change the criterion easily, by changing the content of the cell, instead of changing the formula.

When you need two criteria the format looks like this:

(range=criterion)* (range=criterion)

And 3 criteria:

(range=criterion)* (range=criterion)* (range=criterion)

And so on…

When applied to our situation, it looks like this:

(B:B=J2)* (C:C=J3)

Because our date of birth is located in column C and our criterion is entered in cell J3.

First and Second Criteria Explained

And our formula looks like this:

=INDEX(A1:G55,MATCH(1,(B:B=J2)*(C:C=J3),0),7)

Now the formula is almost ready to go.

Before you press “Enter” you MUST read the next step (or else it won’t work).

Step 5: Ctrl + Shift + Enter

Array formulas are a bit different from normal formulas – especially when it comes to executing the formula.

When you are done with the formula DO NOT press ENTER.

Instead, you press:

Control Shift Enter shortcut

REMEMBER:

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)

After this, we get an error that tells us that the value is not available.

Value not available error

That’s because we haven’t entered the 2nd criterion (date of birth) yet.

So now the formula is looking for:

  • An employee with last name = Jones
  • Date of birth = none.

Hopefully, someone like that doesn’t exist in the data.

We’re looking for a David Jones who is born on February 14th 1975.

Enter 2/14/1975 in cell J3 and look what happens in cell J4.

Returning the answer

A wild number appears!

And that’s how you use a MATCH INDEX formula with multiple criteria.

If you would like to be even more certain that this is our guy, we could add a third criterion. Then the formula would end up like this:

The entire formula

=INDEX(A1:G55,MATCH(1,(B:B=J2)*(C:C=J3)*(A:A=J1),0),7)

CLICK HERE to try our free Excel training.

2017-02-28T08:45:52+00:00

Send this to friend