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:
- We will insert a normal MATCH INDEX formula
- 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:
- 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
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.
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].
Now your formula should look like this:
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.
Your formula should now look like this:
Now we’ll enter the INDEX function around the MATCH function.
Your formula should look like this by now:
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:
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:
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.
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.
Your formula should look like this:
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…