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, which include OFFSET MATCH MATCH, VLOOKUP HLOOKUP and VLOOKUP MATCH, that you should learn to become adept in database theory.
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.
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:
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”.
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.
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
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
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
Step 4: For the final argument in the MATCH formula, input 0 to perform an exact match and close out the MATCH formula
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
Step 6: For this lookup array, select the entire top header row of the original grid you selected for the INDEX formula
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
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”.
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.
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
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
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
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
thank you Laura..
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
Aligrht alright alright that’s exactly what I needed!
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 ) )
Thanks for the catch. I must have flipped those when I was creating the example. The syntax has been updated.
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??
What you are trying to do would use the INDEX function.
Can you explain this using the example? What would my formula be? …If I want to know the state and year for $261.04
Here’s the way to to it in reverse : Be sure to Ctrl+Shift+Enter the forumulas below !
Using the same references as in the first picture i.e. “State” is in A1
To get WA :
=INDEX(A1:A9,MIN(IF(B2:E9=261.04,COLUMN(B2:E9))))
To get 2004:
=INDEX(A1:E1,MIN(IF(B2:E9=261.04,ROW(B2:E9))))
Feel free to replace =261.04 by =your referenfe cell.
Regard,
David
If you want the two on a same cell use :
=INDEX(…)&”-“INDEX(…)
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)?
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?
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.
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.
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.
Will this work if I have, for example, multiple rows with WA data?
That’s great! Thank you!
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!
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
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.
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.
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”)))
Dudz, I’m trying to use this formula to do almost something similar. Can you help me?
=IFERROR(IF(VLOOKUP(E3,SNL!F:BC,1,0)=E3,E3,IF(VLOOKUP(F3,SNL!F:BC,1,0)=F3,F3,IF(VLOOKUP(G3,SNL!F:BC,1,0)=G3,G3,IF(VLOOKUP(H3,SNL!F:BC,1,0)=H3,H3,”Not Identified”)))),” “)
very useful approach – index Match Match and Matrix lookup, for us in developing world, it is very helpful.
Regards
Wilberto Msigwa
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
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
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
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?
Use the If(usurer formula… then it will leave it blank
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
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
Hey! thanks for the tutorial..i was wondering, how to do the exact opposite of what you did? what i mean is, how to construct a table which has state and year on y and x axis respectively, from a table in which both are in the same dimension. Basically constructing table a from table b..reverse of your example
Hi
This is a very good article and thorough example! Thank you for posting!
Just wondering, I have a data table with all the payroll information in columns and with account codes, however, some payroll categories are using the same account codes. and therefore I require a sum functionality. The added issue is that each person can be paid in different currencies and have different departments. I have to sum up all the cost centres and the currencies (which are in rows) and with the same account codes (In column) The way round it I have found is sumproduct where I can perform a multiple lookup for the columns and sum up all the tables with the given account codes.
I want to know if there is another way to do this calculation as sumproduct takes a long time to calculate and I am investigating if Index and Match formulas can give me a faster calculation.
Thanks
Vivian
Hi Team,
Please help on the below.
R O U T E ABC XYZ MNZ RXZ LOWEST
Bangalore 100.00 200.00 300.00 150.00 100.00
ABC Quoted lowest price in the above table
Tranporter name need to be refelct in Next coloum
can you write formula
Thank for the tutorial. Much appreciated.
However, I have a question following on from this. Is it possible to use the same formula to display an exact value using two vertical conditions at either ends of a matrix and the horizontal condition in the matrix ?
i.e.
Using your matrix in this tutorial [assume column 5 was replaced by a name of county from that state]. Is it possible to match a state (vertical column) to its corresponding county (Vertical column) before returning the exact value (the result) in a particular year (Horizontal column)?
Kind regards
Thank you for writing this article – was exactly the solution I needed!
I have a need to sum the values for a specific row. In the case of this articles data, the problem would look like this:
Return the sum sales for a specified state for years 2002-2003.
I need the range of years to be dynamic and consecutive, meaning a user can fill in a start and end year (ie, ’02-’03, ’02-’05, etc.).
Can you please assist? Much appreciated!
Did you figure this one out? I have the same problem at the moment.
Please assist with this scenario:
let’s say in your example you have an additional column called Region:
Region State 2004 2005 2006 2007
WC NY XXX XXX XXX XXX
WC FL XXX XXX XXX XXX
EC OR
EC CA
WR AR
WR TA
In this case how do we pull the same information??
Don’t worry I have figured it out!
=+INDEX(Act_Volumes!$D$3:$O$240,MATCH($B2&$C2,Act_Volumes!$C$3:$C$240&Act_Volumes!$B$3:$B$240,0),MATCH($A2,Act_Volumes!$D$2:$O$2,0))
Can I have the VBA code for what you have mentioned : =INDEX(Act_Volumes!$D$3:$O$240,MATCH($B2&$C2,Act_Volumes!$C$3:$C$240&Act_Volumes!$B$3:$B$240,0),MATCH($A2,Act_Volumes!$D$2:$O$2,0))
Thanks,
🙂
I have been using Index match match in my deliverables workbook to consolidate data from my master file workbook. I have these in 2 different workbooks due to their massive size. My index match match on my deliverables file only returns data when the master data file is open otherwise I get a #VALUE! error. Is there a way to write the formula so I don’t have to have the master data file open in order to look at the delieverables file?
Many Thanks
I need help with INDEX MATCH. I undestand that index match will return a value higher than, lower than, or equal to your lookup value based upon entering a ‘1’, ‘-1’ or ‘0’ at the end of your formula. But I need it to return the value that is NEAREST to my lookup value.
Please help!!!!!
Thank you so much
Hi, and what if I have to sum all of the results of the index/match, and not just return the first value matching?
This is the formula I’m using now:
=INDEX(Hires!$A:$BA; MATCH($A15;Hires!$B:$B;0)+4; MATCH(HR!AG$2;Hires!$1:$1;0))
Thanks in advance.
Great!
I use Vlookup more often than I should…
For complex models it turns very slow.
The performance with INDEX MATCH MATH is awsome.
Thanks!
working brilliantly thank you
This example was very helpful. My one question is if the matching criteria are not exact values how would the formula change?
What if the criteria you are looking for is a range of numbers. From example one of the y axis columns is 0-100,000 and the next column is 100,000-200,000
Great post! I almost didn’t leave a comment, but you made my job that much easier. I love how dynamic this allows excel to be. Thanks again
what if the criteria you are looking for is a range of numbers? how would this change the function?
Hi my friend,
Index Match Match is really a powerful tool. i believe in this simple tutorial a simple vlookup Match would do the trick, with less syntax.
=VLOOKUP(State , Array , Match(Year) , Exact Match)
=VLOOKUP(B3,B11:F19,MATCH(C3,B11:F11,0),FALSE)
We can keep index match match for more complex workbook.
Hi all, I need the opposite of that ..
I’ve a matrix , I want excel to lookup in this matrix for exact match to my lookup value then returns the left most column or the top most row ..
how to do that ?!!
Hi, This was a great explanation and I got it to work well! I want to tweak my formula a bit more so that it will do the index/match lookup below (formula is working) but also use the information found on different worksheets within the same workbook — automatically changing the formula based on a column that contains the name of the workbook to use automatically… perhaps a INDIRECT command???
My main worksheet is called ‘JaxDentist’ There are various worksheets in the same workbook that are used to look up the information and return it to JaxDentist. Examples of the secondary worksheets being used to return data for the index match are called 315-09E, 310-13E, 155-16A, etc. On my worksheet Jax Dentist where data is being returned to, I have a column set up “X” that contains the name of the correct worksheet that excel should use to do the INDEX MATCH, but I can’t figure out the complete formula….
So here is my working formula for a cell to look up info on worksheet ‘315-09E’ for the data.
=INDEX(‘315-09E’!$A$14:$R$37,MATCH($U353,INDEX(‘315-09E’!$A$14:$R$37,,1),0),MATCH($Y353,INDEX(‘315-09E’!$A$14:$R$37,1,),0))
This is working correctly, as long as I specifically name it in the formula which worksheet to us to use (right now called 315-09E)… I
Here’s a second example of a working formula from a different row. This uses a another worksheet to return the same formatted (but different values) using the INDEX/MATCH
now using worksheet to look up “310-13E” so the working formula is =INDEX(‘310-13E’!$A$14:$R$37,MATCH($U353,INDEX(310-13E’!$A$14:$R$37,,1),0),MATCH($Y353,INDEX(‘310-13E’!$A$14:$R$37,1,),0))
I have a column “X” in my JaxDentist worksheet that has the names of the worksheets that excel should use automatically use/substitute into the formula…
How can I change these formulas so it will do continue to do the index match, but also variably also use the worksheet name that is contained in the column x of worksheet “JaxDentist” rather than me specifically naming the worksheet. I keep reading about INDIRECT command but can’t get it to integrate with the INDEX MATCH FORMULAs above. The data I am working with has thousands of lookups to do across 15-20 different worksheets in the same workbook.
Is there any alternative to adding multiple Index Match functions together? For example, I have about 30 columns with numbers in them, and I am currently using Index Match when I have 3 or 4 dollar amounts to add together for the total. However, I now need to add all 30 columns together to find the total for operating profit (which is a row) for example. I can’t use a smiple Sum formula, because each month the number of columns may change.
Any thoughts?
Love your post. Thanks for your help!
I corrupted this formula somehow, and when i try to correct the vertical lookup value, “Sum!$A$3” to “Sum!$A$2” I get an error message that reads “The value you entered is not valid.” Am in a Windows 7 Pro environment in Excel 13. Thanks for any thoughts
=INDEX(Data!$A$4:$BA$61,MATCH(Sum!$A3,Data!$A$4:$A$61,0),MATCH(D$1,Data!$A$3:$BA$3,0))
I am using the following code in order to identify the Facility number associated with a facility. I have 2 issues; firstly there are multiple facilities with the same or similar name and the code search requires an exact name to use for the search. So if I have “Store ABC” and “QCI Store” the code will not pull up either if I just enter Store.
How can I make the search more flexible to react on an incomplete name?
=PROPER(INDEX(‘Facility Database Download 2’!$A$3:$B$4600,SMALL(IF(‘Facility Database Download 2’!$A$3:$A$4600=$R$4,ROW(‘Facility Database Download 2’!$A$3:$A$4600)),ROW(1:1))-2,1))
I am adjusting the last 2 characters on eight extra lines to give me the 4 preceding and 4 post facilities based on the entered facility name however the requirement to enter the facility name exactly as it is in the database is very restrictive. Grateful for any help or advice.
this is awesome! got my assignment done because of this! thanks!!!!!!!!!!!
Hi,
Need help…
I have Three columns Col1- Rest Name, Col2-Locality col3-status. This is my Master Data.
Now, In another sheet I have Rest Name and Locality. Now, if Rest name and locality is match then it should give status in the third column.
Hello! I need to fit an item to a selection of crates. Is there a variation of this formula that will allow me to find all of the crates with dimensions greater than the item?
Item dims = 51 x 47 x 35
array of available crate sizes is as follows
Crate # L W H
1 124 63 55
2 79 37 79
3 57 52 46
4 64 36 54
5 93 64 39
6 93 63 63
I have a table which has clients in one column who are assigned to a risk rating,in descending numeric order in another. I need to find a way to take the top & bottom 5. I can do the index match but I do not want redundancies . so if i have 2 client names with the same risk score, I want to be able to list the first and the second and more if need be. this is what I have for 2 clients so far but it wont work if there is more than two. =IF(E8E7,INDEX(clients,MATCH(E8,rating,0)),INDIRECT(“A”&ROW(INDEX(clients,MATCH(E8,rating,0)+1))))
any suggestions?
Hi,
is it possible to use this formula to match similar co-ordinate elements in an established data set with another data set containing similar values? then outputting the difference between the two data sets? for example, i have to complete grid set out works for piling job. each pile has a name and co-ordinate associated with it (e.g. Piles – 1A-2Y, 1A-2X, would have an associated co-ordinate value i.e. an Easting, Northing and Elevation). Lets say pile 1A-2Y has co-ords North 500, East 300 and Elv 100. i have to record the true value of these grid points in the field then i have to compare their value to the design value. Lets say i record North 499, East 301 and Elv 99 for the same pile (1A-2y) is this possible for excel to match the name of the pile in each data set then output the difference in the easting, northing and elevation ??
thanks.
Is it possible to use the matching function to look at multiple variables? I am trying to build a quote spreadsheet that has to due with equipment and specs. My reference spreadsheet will have make, model, term, and price. So 3 variables have to match and then it will hopefully give me the price. There may be 4 or 5 variables that have to match thus giving the customer the sales price. This is to avoid having someone go through a massive option sheet and I am using a drop box format that will prefill the price and is linked to a large option spreadsheet. Again, I want the price to fill automatically by the matching method.
Make Model Mast Price
(column format)
Thank you
Hi,
Suppose I want to do the exact opposite of what is done in the example. I want to search in a given column for an exact match and then return the value in the corresponding row. How do I do that?
I would like to mention that I have several cells in the column that have exactly the same entry. I would like to seperate the search for each of these cells and return the corresponding values of the rows seperately.
My Excel connects manners of transport to certain destinations. The manners of transport are seperated in different columns and the destinations in rows. The manners of transport can be connected to several destinations, so that’s why I have numerous cells that have the same entry.
Can anyone help me with this problem?
Thanks
Thank you very much for this detailed explanation. One question. How can this INDEX MATCH MATCH formula change to reproduce a whole column’s information? Thanks
Matthew, thank you so much for this! This is absolutely genius!! Very informative and the explanation of the individual portions of the formula are very helpful in ensuring that I’m using it properly for my application.
Quarter Financial_Year_ Shipments Consumption Overall_Accl
Q3 ’12 FY 12 504 250.5320736 253.4679264
Q3 ’12 FY 12 882 1730.127187 -848.127187
Q3 ’12 FY 12 6600 5413.245606 1186.754394
Q3 ’12 FY 12 6912 6752.508728 159.491272
Q3 ’12 FY 12 31360 33986.59375 -2626.59375
Q3 ’12 FY 12 7440 11070.56682 -3630.56682
Q3 ’12 FY 12 0 0 0
Q3 ’12 FY 12 10822.944 6933.698006 3889.245994
Q3 ’12 FY 12 3920.49 1312.156901 2608.333099
how you apply index match match function to attain the shipment values from the 5th row. as the quarter column contains same value throughout the column ?
Hi, I am using an Index and Match function. The correct answer is provided in the formula bar however only N/A is shown in the cell.
Why does it not show the correct answer?(such as in the formula bar).
(i have checked whether the text is the same format)
Hi Leone,
It’s difficult to tell what the issue is without full context, but it might be worthwhile to go through this list of potential errors with INDEX MATCH below:
http://www.mbaexcel.com/excel/top-mistakes-made-when-using-index-match/
Let me know if that helps.
Best
Matthew
I have employees that are given a “bonus” each year. The bonus is a set amount depending on which group the employee is in and when their hire date was. For example if you belong to Apple group and were hired between 01/01/2000 and 12/31/2005, your bonus would be 200.00. If you were in Apple group and hired 01/01/2006 to current, your bonus would be 150.00. If you were in Banana group and hired between 01/01/2002 and 12/31/2005 your bonus would be 75.00. If you were in Banana group and hired 01/01/2006 to current, your bonus would be 50.00
How can I get an index match formula to look up a group based on a hire date and return the correct bonus amount?
Hi Wendy,
This is very interesting question, as for this particular lookup, you’re looking to do an exact lookup for one variable (employee group) but a range lookup for the other variable (hire date). You can actually still just use the INDEX MATCH MATCH formula to achieve this. All you have to do is, for the hire date, use the approximate match option. You can read up more on using approximate match with MATCH in Excel here:
http://www.mbaexcel.com/excel/how-to-use-the-excels-match-formula-to-assign-histogram-bins/
When referencing the hire ranges, just make sure to use the lower bound when doing the lookups. In the example above that would be 1/1/2000, 1/1/2002, and 1/1/2006. This is all based off the assumption that there are no breaks in your hire date ranges.
Let me know if that helps.
Best,
Matthew
Could you help me with an issue? I wanted to use a formula that finds a value, “RA”, and returns the full name of the “RA” in column V when BuildingName and RoomName match.
=FIND(“RA”,[Role],INDEX([First Name]&” “&[Middle Name]&” “&[Last Name],MATCH($C2,[BuildingName])*(F2,[RoomName])))
The table has the data in over 9,000 rows and column “U.” Only 210 rows have “RA” in column “Role” (O). I needed to assign the RA to each SubjectName (1 per row) based on the BuildingName and RoomName. There is only one “RA” that shares those two attributes with the multiple SubjectNames, 30-97. The RA’s names are also in the same columns as of the SubjectNames (first middle and last–3 columns).
Thank you,
does any body knows how i use index MATCH MATCH MATCH
3 MATCHES
This is great! Thanks
Can you help me understand which type of formula would be best for me?
I want to use our data file to automatically update multiple products on a weekly basis.
The data file has data in this format:
Week1 Week2 Week3…
#1 Units Units Units…
#2 Units Units Units…
#3 Units Units Units…
#…
My weekly files are in the same format…I just want to search through the entire column with product #s in in to match to the weekly sheet, then place the correct week’s data in the corresponding cell. I was using vlookup but that doesn’t seem to work well for more than one specific week’s data.
Thanks for any help!
hello All,
I need your help. I am looking for a formula that can search a value in a table or a list and give me the header of the value that i am looking for.
thanks.
Dear All,
If I have data as per below:
A1 : Pen B1 : $1.20 C1 : $1.40 D1 : $1.60
A2 : Eraser B2 : $0.50 C2 : $1.00 D2 : $1.50
A3 : Paper B3 : $2.00 C3 : $2.20 D3 : $2.40
If I look up for ‘Pen’, I need the return in multiple results vertically, like below:
A5 : Pen
Results in B5 : $1.20 B6 : $1.40 B7: $1.60
Is there any possible formula for this?
Thanks in advance for your help and advice.
Best regards,
Lis
I have the same question as Mahmoud Ahmed. I have soccer data which contains Home Team, Away Team, Home Score, Away Score, with which I want to update a league table. So the formula needs to find the home team (In a row), the away team (in a column) and allocate the score to that cell. I’m thinking Index Match Match Match – is this possible?
Matthew, thank you so much! No one has ever explained Index & Match fucntions better. For that matter, your Excel tutorials are simply the best! I love this site and will share with my associates!
Best,
Abhi
Hi, this is a nice formula and very clearly explained. However, it does not help me as I need to cross reference 3 criteria in 3 columns and 2 criteria in 2 rows to return a number.
My file has in column A cost centres, column B regions and column C type of expense. Across row 1 I have category like budget, actual, forecast and row two, qtr 1, qtr 2, etc.
I am trying to summarise on the front sheet each cost centre per region per expense type but also if it is budget, actual or forecast and what quarter.
As this tab is a new essbase retrieve I cannot add any extra rows or columns to it or the retrieve won’t work.
This has been giving me headaches as I do not want to do this in numerous steps and open it to error.
I tried, SUMPRODUCT, INDEX MATCH MATCH or simply SUMIFS but it is not working.
What can I do?
I have a data file that consists of 2 columns (A=client ID # B = service dates). Each client has many service dates and I want to select the first service date that belongs to the client. ie: Client 1 has 4 rows with 4 service dates (Jan 2015, Feb 2015, Mar 2015 and Apr 2015). I am able to sort the data range so that the first service date is the top row for each client. I want some sort of formula that pulls Client 1 Jan 2015 and that I can run it for all the different clients. Any help would be much appreciated!!
Thank you! This worked PERFECTLY for my situation — and greatly sped up my data-crunching. Appreciate it!
Hello, could you tell me if it is possible to use this formula to search a cell from sheet 1 to find its match on Sheet 2, then compare against an additional cell to return a date in a separate column?
For example:
Order number 123456 is on sheet one. I want to pull an invoice date over.
On Sheet 2 a row contains the order #, warehouse # and the invoice date. The order # is listed on 2 separate rows though because it is broken down by warehouse 1 and then warehouse 2.
Sheet 1 Column A Invoice Date WHS01 Invoice Date WHS02
Row1 123456 ? ?
Sheet 2 Column A B C – Invoice Date
Row1 123456 WHS01 05/26/16
Row2 123456 WHS02 05/27/16
I cannot use vlookup due to the duplicate order numbers on sheet2 but have not been able to find a formula that can accomplish this yet.
Hello,
I have tried to get this to work on a fairly simple data set, but cannot seen to do so.
I want to lookup the day on which the maximum energy was generated from the following figures. The left hand column is the day. The maximum is 13.7 and should return 31. Instead, I get a N/A. It makes no difference if the cells are formatted as Numbers or General.
This is my formula: =INDEX($A$4:$A$34,1,MATCH(MAX($C$4:$N$4),$C$4:$N$34,0))
#N/A 13.37
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1 2.00 4.00 6.69 8.00 13.00 8.00
2 1.00 2.00 4.00 7.00 6.69 11.00 9.90
3 2.00 6.00 5.00 6.69 12.00 10.00
4 1.00 2.00 5.00 5.00 6.69 12.70 9.60
5 2.00 4.13 6.69 6.69 9.30 8.40
6 1.00 2.00 4.20 5.00 6.73 11.90 7.50
7 1.00 2.82 5.00 5.00 5.00 11.20 6.50
8 0.50 2.82 4.50 5.00 9.00 9.50 5.90
9 0.05 2.82 4.40 6.50 6.69 7.00 5.40
10 2.82 4.30 6.69 6.69 0.00 7.10
11 1.00 4.50 4.00 6.69 6.99 7.00 5.00
12 1.00 4.63 4.00 6.69 6.69 7.00 5.20
13 1.00 3.30 4.40 6.69 6.99 6.20 6.40
14 1.10 4.40 4.00 6.69 6.69 3.00 9.40
15 0.90 6.40 4.00 6.69 6.38 3.00 4.10
16 0.50 7.60 4.30 6.69 6.69 2.00 4.70
17 0.50 1.00 3.10 6.69 6.69 8.30 9.40
18 1.00 4.00 1.50 6.69 6.69 3.70 12.60
19 1.00 3.50 1.00 6.69 6.69 5.80 11.70
20 1.00 3.60 4.30 6.69 6.30 4.30 7.80
21 1.00 3.60 2.90 6.69 6.11 7.50 9.10
22 2.00 3.60 4.30 6.69 6.69 4.70 6.50
23 2.00 3.60 1.00 6.69 7.30 5.60
24 1.00 4.00 1.60 6.69 7.20 5.20
25 2.81 3.67 8.00 6.69 6.69 4.80
26 2.83 3.80 3.90 6.69 6.69 6.00
27 2.82 3.30 6.00 6.69 6.69 7.00
28 2.82 6.30 4.70 6.69 7.20 5.60
29 2.82 3.60 5.30 6.69 6.69 2.50
30 2.82 4.30 6.69 6.69 7.30
31 2.82 13.37 7.74
39.29 101.68 135.50 192.37 211.36 204.10 170.20 0.00 0.00 0.00 0.00 0.00
Grateful for any suggestions where I am going wrong.
Anton
Great explanation of Index Match Match. I refer people to this regularly.
Thanks,
M
Hi, Great post. It works well for what I need. My question is when you set your range of the matrix can you use a table name instead of a range of cells so the index match match can be used to search the left most column and the header row of the specified table for the result also allowing for the matrix size to get bigger and not having to adjust the formula?
Below is my database table. Below that is my horizontal and vertical references just above the outcome. I am trying to figure out how to incorporate a 2nd horizontal option. I need to be able to A. choose the builder B. choose the door and C. choose the wood then return the correct value. Any suggestions? I also copied my whole formula at the very bottom. Thanks in advance.
A B C D E F G H I
1) Knotty Alder Beech Paint/MDF Paint/Beech
2) RP FP RP FP RP FP RP FP
3) Builder A $28.50 $27.50 $30.50 $29.50 $28.50 $27.50 $30.50 $29.50
4) Builder B $27.50 $26.50 $29.50 $28.50 $27.50 $26.50 $29.50 $28.50
5) Builder C $28.50 $27.50 $30.50 $29.50 $28.50 $26.50 $30.50 $29.50
6) Builder D $27.50 $26.50 $29.50 $28.50 $27.50 $26.50 $29.50 $28.50
Vertical Horizontal 2nd Horizontal
Builder A RP Beech
Result
$28.50
=INDEX(A1:I6,MATCH(A$10,A1:A6,0),MATCH(B$10,A2:I2,0))
Hello Guys,
I am using the formula to match dates from 2 sheets and give me a certain value. Is there a way to use this formula to look up values from more than one sheet?
Thank you for your help
=IF(ISERROR(INDEX($D$1:$F$1203,SMALL(IF($D$1:$F$1203=$A$2,ROW($D$1:$F$1203)),ROW(1:1)),2)),””,INDEX($D$1:$F$1203,SMALL(IF($D$1:$F$1203=$A$2,ROW($D$1:$F$1203)),ROW(1:1)),2))
Hi,
I hv a dataset in excel like dis,
CLASS SUB-CLASSES NO. OF PROTEINS
A a 10
A b 0
A c 3
A d 8
A e 15
B a 21
B b 3
B c 13
B d 0
B e 8
and I need to arrange it as follows,
CLASS/SUB-CLASSES a b c d e
A 10 0 3 8 15
B 21 3 13 0 8
means, I need to take into account, 2 columns and one row. Kindly help
I am trying to retrieve information from hundreds of tabs in a closed workbook (Excel Formulas IIA.xlsx) and have them populate my recap workbook (Excel Formulas II.xlsx). I have used the INDIRECT formula (=INDIRECT(“‘[“&$A7&”.xlsx]”&$B7&”‘!”&C$4 where A7 is the file name Excel Formulas IIA; B7 is the tab name Sheet2; and C4 is the cell reference E4.). The full formula (no substitution) being =INDIRECT ([‘Excel Formulas IIA.xlsx]Sheet2’!C$4). This does not, however, allow me to update my recap workbook when the source workbook (Excel Formulas IIA) is closed.
I have tried to use the INDEX MATCH formulas, but can only get them to work when the full source workbook, tab, and cell/cell range is entered-they do not work when using a substitution for the workbook, tab, and cell/cellrange. My original/test formula works and is =INDEX(‘[Excel Formulas IIA.xlsx]Sheet2′!$E:$E,MATCH(C1,'[Excel Formulas IIA.xlsx]Sheet2’!$C:$C,0)). When substituting formulas for the worksheet name and tab name, I get a formula of =INDEX(“‘[“&A8&”.xlsx]”&B8&”‘!$E:$E”,MATCH(C1,”‘[“&A8&”.xlsx]”&B8&”‘!$C:$C”,0)) where A8 is Excel Formulas IIA and B8 is Sheet2, the new formula is I receive the result of #VALUE. When stepping through the Evaluate Formula function within Excel, the MATCH section ends up failing.
Is there a way to use an INDEX MATCH with substitutions/formulas for Workbook, Tab, and cells/cell ranges?
Hello.. I now actually trying using Index match/IF function to try to index the value from “ColE” onto “ColB” , by matching “ColA” compare with “ColD”.
For my eg.
Shown below “Urr” got 3 different value in “ColE” how to I match and insert the three “Urr” with the different number
result in “ColE” onto “ColA” with the same exact result number should appear in “ColB”???
And “colA” “Usw” data still match with “ColD” “Usw”even is not in the same row?
something like new data replace with original data orderly, plus without deleting original data if the data don not appear in new data.
Eg.
ColA
Doman
Urr
Usw
ColD ColE
Doman 1234
Urr 12345
Urr 12345
Urr 12346
Perfect, thanks for the wonderful solution @MATTHEW KUO 🙂
What a great tool! Question Though- I used list boxes for my vertical and horizontal lookup. Some of the outputs are formatted in dollars and some general numbers or dates. How to I get the formatting to carry back to the output of the lookup?
I am trying to create a formula using a number in cell B1 and the data in a range of cells in column B on one worksheet and finding the result in another worksheet. The problem is, I want excel to find the number shown in B1, which could be in any column on row one of another sheet and then finding the result for the next lookup criteria, which could be in one of two places….if found in the first group and result is blank, look in the next group to find the data.
In column D of one worksheet, row 19, I want to find the result for the following:
B1 = 1418
B19 = Pick up trash
Look in another worksheet and find the column that contains 1418 in row 1 –
find Pick up trash in column A (could be in two locations and the first location would not contain any data for 1418, so will have to look for the row that contains the data for 1418)
Hi 🙂
Can anyone help me to find a formula to sort out only ‘sams’ value,
A B
SAM 10
SAM 25
ERINA 11
SAM 30
I tried to use Index match function but, it only gives result for first match.
=VALUE(IFERROR(INDEX($AB$7:$AB$497,MATCH(AH2,$Z$7:$Z$497,0)),0))
BIG Thanks in advance
In such of some solutions I ran across mbaexcel.com and what an excellent virtual teacher. The content was really helpful. Using index-match-match and the earlier vlookup-hlookup combinations; simply well explained. Thank you for the good job.
In search of some solutions I ran across mbaexcel.com and what an excellent virtual teacher. The content was really helpful. Using index-match-match and the earlier vlookup-hlookup combinations; simply well explained. Thank you for the good job.
Thanks for the great knowledge sharing and tutorial!
Do you have any video of that? I’d like to find
out more details.
Hello.
I’ve been using the INDEX MATCH MATCH formula for a while, but yesterday I tried to use it to implement an ASTM table. The formula returns the desired values from a certain rank.
For example in column I have temperatures from 0 to 150 degrees and in lines I have density ranging from 0.690, 0.691, 0.692, …, 0.909.
The formula works very well up to the density of 0.812. After if I enter a density of 0.850 for a temperature of 30 it returns to me the value which is at the intersection of 30 and 0.849
How can you help me?
0.690 0.691 0.692 0.693 0.694 0.695
0 0.6598 0.6609 0.6619 0.663 0.664 0.6651
1 0.6604 0.6614 0.6625 0.6635 0.6645 0.6656
2 0.6609 0.6619 0.663 0.664 0.6651 0.6661
3 0.6614 0.6624 0.6635 0.6645 0.6656 0.6666
4 0.6619 0.663 0.664 0.665 0.6661 0.6671
5 0.6624 0.6635 0.6645 0.6656 0.6866 0.6676
6 0.663 0.664 0.665 0.6661 0.6671 0.6682
7 0.6635 0.6645 0.6655 0.6666 0.6676 0.6687
8 0.664 0.665 0.6661 0.6671 0.6681 0.6692
9 0.6645 0.6655 0.6666 0.6676 0.6687 0.6697
Great, thank a lot