How to Use INDEX MATCH MATCH

by Matthew Kuo on September 7, 2013

in Database Theory, Excel

If you’re using Excel and you’ve already learned how to use INDEX MATCH, you’re well on your way to becoming proficient with Excel lookups.  What INDEX MATCH MATCH offers you is a more powerful version of the formula.  Instead of just a vertical lookup, INDEX MATCH MATCH allows you to perform a matrix lookup, which is also known as a two-way lookup.  This combination formula may initially seem complex because of its three individual formulas, but after you understand each component and how they interact, using this tool will become second nature to you.  INDEX MATCH MATCH is one of several lookup formulas you should learn to become adept in database theory.

INDEX MATCH MATCH 12

Objective and When to Use

There’s really just one key condition that needs to be met before you can use INDEX MATCH MATCH:

A matrix lookup can only work if your data table has lookup values on both the top and left hand side.

Basically, your data needs to be in a matrix format.  People usually create matrixes, with lookup values both vertically and horizontally, to cross reference two different fields.  In the example below, we are cross referencing the field State with the field Year and showing the relevant data point for Sales.

INDEX MATCH MATCH 01

Creating a matrix saves you space within your spreadsheet and is more visually appealing.  However, most data sets are not organized in this fashion.  In fact, if you follow proper database theory, your data actually should not have lookup values going both vertically and across your table.  A properly formatted table would look like the example below:

INDEX MATCH MATCH 02

Before moving forward, ensure that you are using the proper formula for your data set.  There are several other lookup options you can choose from if your data does not fit the requirements for INDEX MATCH MATCH.  For example, if you only have lookup values on the top of your data set, you should consider using HLOOKUP.  If you only have lookup values on the very left hand column of your data set, you should consider using VLOOKUP or INDEX MATCH.

The Syntax

Below is the syntax for using this formula combination.  Don’t worry if it doesn’t make sense now; the rest of this post will provide context for each component and we’ll review a more practical version of the syntax that’s easier to remember.

= INDEX ( array , MATCH ( lookup_value , lookup_array , 0 ) , MATCH ( lookup_value , lookup_array , 0 ) )

Not surprisingly, INDEX MATCH MATCH is based on the INDEX and MATCH formulas, which we will now go through in detail.

The INDEX Formula

The INDEX formula asks you to specify a reference within a range and returns a value.  In its simplest form, you just indicate either a row or column as your range, specify a reference point, and the value that matches that reference point is returned.  For example, if we were to select the left hand column of this table, and specify the reference “6”, the INDEX formula would return the value “WA”.

Now instead of using just selecting a single row or column, what you can also do with the INDEX formula is select an entire matrix, with multiple rows and columns, as your array.  The key difference here is that, instead of just specifying a single appearance order as a reference, you must now provide both a vertical and horizontal reference to return your value.  (Please note that the INDEX formula always takes the vertical reference first)  Using the INDEX formula with a matrix reference represents the foundation of utilizing INDEX MATCH MATCH.  The syntax for the INDEX formula by itself is as follows:

= INDEX ( array , row_number , column_number )

For example, let’s say we selected the entire sales data table, and then specified “6” as the row number and “4” as the column number.  The INDEX formula performs the intuitive action of going down 6 rows and over 4 columns with the range we selected to return the value of “$261.04″.

INDEX MATCH MATCH 03

The MATCH Formula

The MATCH formula asks you to specify a value within a range and returns a reference.  The MATCH formula is basically the reverse of the INDEX formula.  The two formulas have the exact same components, but the inputs and outputs are rearranged.

= MATCH ( lookup_value , lookup_array , 0 )

To give you an example of the MATCH formula, if we were to select the entire left hand column and then specify “WA” as our lookup value, the MATCH formula would return the number “6”.  Please note that you have to put in a “0” as the last argument to ensure that the MATCH formula looks for an exact match.

INDEX MATCH MATCH 04

How it Works

As mentioned before, when using the INDEX formula across a matrix it requires both a horizontal and vertical reference.  The only additional complexity that INDEX MATCH MATCH adds is that the vertical and horizontal references are turned into MATCH formulas.

Putting it Together

Below is a simplified version of the syntax describing the inputs with the appropriate context for our goal.  In case you get lost in the individual steps, you can always refer back to this notation.

= INDEX ( entire matrix , MATCH ( vertical lookup value,  entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )

Step 1: Start writing your INDEX formula and select the entire table as your array

INDEX MATCH MATCH 05

Step 2: When you get to the row number entry, input the MATCH formula and select your vertical lookup value for the lookup value input

INDEX MATCH MATCH 06

Step 3: For the lookup array, select the entire left hand lookup column; please note that the height of this column selection should be exactly the same height as the array for the INDEX formula

INDEX MATCH MATCH 07

Step 4: For the final argument in the MATCH formula, input 0 to perform an exact match and close out the MATCH formula

INDEX MATCH MATCH 08

Step 5: Now that we’ve arrived at the column number entry of the INDEX formula, input another MATCH formula but this time select your horizontal lookup value for the lookup value input

INDEX MATCH MATCH 09

Step 6: For this lookup array, select the entire top header row of the original grid you selected for the INDEX formula

INDEX MATCH MATCH 10

Step 7: Repeating what we did for the previous MATCH formula, input “0” for an exact match and close both the MATCH formula and the INDEX formula with parentheses

INDEX MATCH MATCH 11

What Excel Does

Excel must first calculate the result of the two MATCH formulas embedded within the INDEX formula.  Since we know that “WA” is the sixth value down in the left hand column, and “2004” is the fourth value across in the top header row, those formulas become the values of 6 and 4 respectively.  Once we’ve simplified those components, Excel essentially performs the exact same INDEX lookup that we demonstrated before; it goes down 6 rows and over 4 columns to pull the correct value of “$261.04″.

INDEX MATCH MATCH 12

Summary

INDEX MATCH MATCH probably won’t be a formula you use often.  Most of the time when dealing with databases and data tables, you’ll be using vertical lookups to query results. However, in situations where you absolutely do need to perform a matrix lookup, INDEX MATCH MATCH is the best option you have.

{ 28 comments… read them below or add one }

Michael Leng September 9, 2013 at 5:51 pm

Thanks for sharing the “INDEX MATCH MATCH” formula. Even this formula has not been used more often because it is a bit complex and contain several syntax.

Anyhow, this is very good tutorial that describe step by step clearly for following.

It’s good job!. I do love it.
Michael Leng

Reply

Laura September 18, 2013 at 9:56 am

Thanks for the great article! I have one important question though….

Can I create an Index Match Match where the values being looked up are in one Excel file and the returned values are being stored in a different Excel file? If so, how do I specify that? If I can’t use two different Excel files (workbooks), can I at least use two different worksheets in the same file and, if so, how do I specify that?

Just to clarify, my situation is that I have a master file that lists a bunch of people who are supposed to take a certain e-course. I can run a report (which can be downloaded as an Excel file) on the LMS that shows everyone who’s accessed the e-course and what their completion status is. I want to be able to add course-completion info to each person in my master list. (The problem with the report from the LMS is that it doesn’t show anyone who hasn’t accessed the course, so I need a way to be able to also see people who should have, but haven’t, accessed the course yet.)

Thanks, in advance, for any help with this that you can give me! :)
Laura

Reply

Matthew Kuo September 21, 2013 at 8:32 pm

Hi Laura,

Yes, you can write formulas across different workbooks, by literally just selecting the cells from those different workbooks. However, this is generally not recommended, because if anything changes with you store your files, the formula will be broken.

Likewise, it’s definitely possible to write this formula within the same workbook across different worksheets. Simply click on the cells you want from the other worksheet, as you are writing the formula. I definitely recommend the latter approach if possible, as it is much more common and generally more stable.

Best,
Matthew

Reply

Matthew Kuo September 21, 2013 at 8:32 pm

Hi Laura,

Yes, you can write formulas across different workbooks, by literally just selecting the cells from those different workbooks. However, this is generally not recommended, because if anything changes with you store your files, the formula will be broken.

Likewise, it’s definitely possible to write this formula within the same workbook across different worksheets. Simply click on the cells you want from the other worksheet, as you are writing the formula. I definitely recommend the latter approach if possible, as it is much more common and generally more stable.

Best,
Matthew

Reply

Paul November 14, 2013 at 9:43 am

Matthew,
nice tutorial, thank you for this. It works for me properly only if I have data sorted in alphabetical order in the first column. What I am doing wrong?
Paul

Reply

Felipe December 19, 2013 at 7:13 am

This is awesome, will save me tons of time. Very clear explenation except i belive there is a small error in your simplified syntax version:
= INDEX ( entire matrix , MATCH ( entire left hand lookup column , vertical lookup value , 0 ) , MATCH ( entire top header row , horizontal lookup value , 0 ) )
the lookup value and lookup column are backwards.

i belive it should be
= INDEX ( entire matrix , MATCH ( vertical lookup value , entire left hand lookup column , 0 ) , MATCH (horizontal lookup value , entire top header row , 0 ) )

Reply

Matthew Kuo December 19, 2013 at 11:05 am

Thanks for the catch. I must have flipped those when I was creating the example. The syntax has been updated.

Reply

Feedback January 20, 2014 at 12:05 am

Does anyone know how to do this BACKWARDS???

As in, can I look up a value and it will tell me the corresponding row and column??

Reply

Rick November 20, 2014 at 1:22 pm

What you are trying to do would use the INDEX function.

Reply

moore January 21, 2014 at 2:06 am

Hi Matthew, Thank you for the good explanation with the INDEX MATCH MATCH, however can it be also be done using two area? INDEX(MATCH,MATCH, area number)?

Reply

dimitris January 29, 2014 at 7:34 am

Hello,

thanks for the formulas. However I have been trying to use this formula but need do the match with two horizontal values but does not seem to work, do you have any idea?

Reply

Alexandra February 4, 2014 at 12:57 pm

Can you have 3 different criteria? Ie – I want to match 1 vertical and 2 horizontal criteria, I tried using index match match match but that didn’t work as I got a reference error.
Thanks.

Reply

Dr Xorile February 13, 2014 at 9:39 am

I was also looking for how to do this backwards (same as Feedback, 20 Jan, 2014). So given 261.04, find the year and the state (in the example given). For example, can you find the biggest sales number (which is just MAX(C12:F19)), and then look up which state and year that was.

Reply

Dr Xorile February 13, 2014 at 10:02 am

One obvious way to do it involves adding another row (or column). So you add another row which has a formula something like this:
=IFERROR(VLOOKUP(lookupvalue,C$12:C$19,1,FALSE),FALSE)

Suppose you put that in C21 and drag it out to the right to F21. That row will have a bunch of FALSEs (meaning that the value doesn’t appear in the corresponding column), and the actual value you’re looking up (lookupvalue).

Then you do a MATCH on that row, to figure out the right column (note that if it appears more than once, you’ll only get the first row containing it, but that’s the way with badly defined reverse lookups!)

=MATCH(lookupvalue,C21:F21,0).
This will give you the column that the value appears in (suppose this is in E3).

=MATCH(lookupvalue,OFFSET($B$12:$B$19,0,$E$3),0)
This will give you the row that the value appears in.

You can use this to get the state and year or whatever you want.

Reply

MelodyG March 28, 2014 at 9:46 am

Will this work if I have, for example, multiple rows with WA data?

Reply

Igor April 6, 2014 at 7:16 am

That’s great! Thank you!

Reply

Ron Abraham April 10, 2014 at 9:49 pm

Matthew – I had an array that I have been wrestling with for 4 weeks – and not until I found your concise, easy to follow instructions was I able to complete my task.
You are a life saver. THANKS!

Reply

Mia May 21, 2014 at 12:14 am

Matthew,
Can you show me how to retrieve match data using index match or if index match across different worksheets?
Example:
Worksheet 1 (contains team numbers. this is where i want to receive data from sheet 2 if the team numbers match)
A1:A3 contains team numbers (A1=1, A2=2, A3=3)
Worksheet 2 (contains teams scores)
A1:A3 contains team numbers (A1=1, A2=2, A3=3)
B1:B3 contains the teams scores (B1=10, B2=21, B3=12)

I want to pull the teams scores from worksheet 2 and place the data into column B in worksheet 1. I keep getting a “0”. Please help

Reply

HE June 5, 2014 at 12:47 pm

Hi,

i have a small problem. i made a matrix for some types of chairs and the different colors that they are available in. the matrx looks somehow like this:
Blue Green Yellow
chair 1 x x
chair 2 x
chair 3 x x x

i would like to be able to execute a lookup for a certain type of chair and as a result get the available colors.

anybody that can help me out?

Regards,

hendrik Evens.

Reply

Charlotte June 13, 2014 at 3:39 pm

I am trying to write a formula that will confirm cell C3 matches with somthing from Column R and if so return “Yes”, if not I want it to look in Column S and if it matches than return “second”, if not I want it to look in column T and if it matches than return “third”, otherwise return “not identtified”.

Is anyone able to assist me? I know there has to be nested formulas in this combination but I am not sure which ones would work most efficiently.

Reply

Dudz October 13, 2014 at 3:28 am

Hope this will work for you.

=IF(VLOOKUP(C3,R2:R4,1,1)=C3,”Yes”,IF(VLOOKUP(C3,S2:S4,1,1)=C3,”Second”,IF(VLOOKUP(C3,T2:T4,1,0)=C3,”Third”,”Not Identified”)))

Reply

Wilberto Msigwa October 11, 2014 at 5:39 am

very useful approach – index Match Match and Matrix lookup, for us in developing world, it is very helpful.

Regards

Wilberto Msigwa

Reply

Dudz October 13, 2014 at 1:12 am

i’m not sure how will i apply this INDEX MATCH MATCH in my table to get the following:
i want to get the latest value of coloum A. coloum B is like the stage PP is the 1st stage DD is Next stage and so forth… the same with coloum C (30, 60, 90). in coloum E i want to have a mark “KKK” if it’s the latest value considering coloum B,C and D:
A B C D E
xx PP 60 5/6/2014
xx PP 90 6/24/2014
yy DD 60 3/24/2014
yy DD 90 6/10/2014 KKK
xx DD 30 7/15/2014 KKK

Reply

Sam October 19, 2014 at 9:00 am

how to make formula that if there are 30 days in a month & if he is Present 27 days add 3 so that he gets full paid but if he is 29 days prsnt how to make formula for full paid

Reply

Anan October 22, 2014 at 1:49 pm

Hello,

This is awesome. thanks. I have a task that is a little more complicated. Lets see if someonr knows how to solve this.

I am matching two different sets of data that may have some common matching numbers.
In the illustration above, we are applying the index match match to match two individual values to those in a matrix and get the desired result.
I need to apply this to two different data sets and display the common values in a third data set.

Am I clear ? should I rephrase?

Lets hope you know how to do this.
Thanks.
Anan

Reply

Leah October 31, 2014 at 1:17 pm

I am currently using the Index Match Match formula across several worksheets. However, the issue that I am having with my formula occurs when one of the worksheets that my Index Match Match formula uses to search through does not contain a match for the master sheet. When this occurs, I receive the #N/A. The formula works excellent if every worksheet has an entry that corresponds to the Master worksheet however not every worksheet will have a match to the master. Any suggestions?

Reply

JJ November 10, 2014 at 11:32 am

Is there any way to choose the 2nd or 3rd matching column value for the column reference? For example, if my column titles are Q1, Q2, Q3, Q4, Q1 is there any way to ensure that I pull the 2nd Q1 value?

Thanks,
JJ

Reply

Anwar November 12, 2014 at 12:44 am

hello,

I have tried this formula =INDEX(Forecast!A4:O58,MATCH(A5,Forecast!A5:A58,0),MATCH(setup!$B$11,Forecast!$A$4:$O$4,0))
but the result is showing 0. even there is some value in the cell

Please help on this regard

Reply

Leave a Comment

{ 3 trackbacks }

Previous post:

Next post: