Quick Navigation
Using INDEX and MATCH to Replace VLOOKUP
What we really need is to be able to look up the Model and the Displacement at the same time. MATCH is a function that gives you the location of an item in an array. The syntax for MATCH is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
The lookup_value is what you are searching for.
The lookup_array is the array of values you are trying to find the lookup_value in.
The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically.
The INDEX function takes a location and returns the value that is in the cell. The syntax for INDEX is as follows:
=INDEX(array, row_num, [col_num])
The array is the table of data that contains the cell value you want.
The row_num is the relative row number of the cell you want.
The col_num is the relative column number of the cell you want.
By combining INDEX and MATCH we can produce the same result as VLOOKUP. Using the same search we did for VLOOKUP, the INDEX/MATCH pair looks like this:
=INDEX(C4:H1159,MATCH(A2,C4:C1159,0),6)
C4:H1159 is the array that INDEX uses to find the value.
A2 is the cell that the value we want MATCH to find.
C4:C1159 is the lookup_array that MATCH looks through to find the value in A2.
The 0 means that MATCH will look for the exact value instead of an approximate one.
Column 6 is the column in the C4:H1159 array that holds the Combined Fuel Economy values.
The result is identical to the VLOOKUP result. MATCH finds the first Combined Fuel Economy value for the Acura ILX, which means it will give 38 MPG for the 1.5 instead of one of the other engine options. To find a specific Model and engine Displacement combination, we need to modify our INDEX/MATCH formula into an array formula.
Thanks for the great post on the use of the combined INDEX & MATCH functions. It will certainly be useful for me in the future. Unfortunately, it falls just short of solving a problem I’m working on now. Can you suggest how this approach might be modified (or another approach entirely) for use with two criteria, one of which is not exact? Specifically, I want to search an array where one value is an exact match, and the second value falls within a range (specified by two columns – an upper and a lower bound). When both of these criteria are met, I want to output the value in a column adjacent to the values that were searched. For example, in the attached image, the cells highlighted yellow are what I’d like to generate a formula to produce (here I determined them by hand). I need to search column A for a match for column F, and then find cases where column G falls between the values in columns B & C. Then I want to output the value contained in column D. Please let me know if you’d like any clarification, and thanks for any help you can provide.
Only need to recognize that you can <, as well as =. For example, in cell H2 enter this formula =INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7<$G2),0),4) remember to enter using Ctrl-Shift-Enter. Then you can copy formula into cells H3 through H13 EDITOR'S NOTE: Chuck, I corrected the formula you provided to the way you intended before approving it...
Hello,
In my scenario, I am using G2 as a date and would like to retrieve the date closest to the date provided on G2. Ideally I need to retrieve the exact match of the date on G2 but at times there will not be an exact match. If this occurs, I need to retrieve the closest date in the range whether it is before or after the date listed on G2. Is a formula for this feasible? Thank you!
This formula (=INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7<$G2),0),4)) works, however I need to retrieve the closest date
Assuming the ILX is in inventory and I would like to know all the colors available; in other words replace the combined fuel column with colors (red, white etc.). In this case there will be multiple ILXs with the 2.4 displacement and a column listing all the colors. How can I get the formula to concatenate all the colors whenever it finds ILX 2.5?
sorry, text editor won’t allow greater-than and less-than symbols.
You just need to recognize that you can use ‘greater-than’ and ‘less-than’, not just ‘equals’ in your comparison checks. (Unfortunately, the equation in the previous post is a mess because it does not show the greater-than or less-than symbols, but instead removed the middle of the formula – the part that mattered the most.)
Hi Jason,
Chuck’s suggestion to use a greater-than or less-than to set upper and lower bounds is right on the money. His formula checks to make sure Column B is less than the Column G item. To be more precise, you could check the upper bound too, like so:
=INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7<$G2)*($C$1:$C$7>$G2,0),4)
Don’t forget to enter as an array formula with CTRL+SHIFT+ENTER!
Hi Robert,
I am having a sheet with names in one column, and in another sheet with names and numbers. i have used vlookup to get the number from sheet 2 to appropriate value in sheet 1. The difficulty i am facing is in sheet 2 same names are there for different numbers, so vlookup is giving the first match value and leaving the rest. Help me to solve this issue
I had the same issue. The way I resolved the issue is to add ” 2″ behind the last name of the second entry for the person(s) with two numbers, and in the results table, you have to have two entries for those people, by adding ” 2″ behind the last name.
If anyone knows a better solution …
can you send some photo of your project so i can understand more.
Its better to concatenate the Name & Number and than match name and name Number. Could have elaborated more if sheets were available
I have been going to this webpage for your formula multiple times per month for the past year. You have made my life so much easier. Thank you.
Thank you for your upper bound suggestion. That solved the problem I was having with Chuck’s suggestion. Very nice!
Excellent & Fantastic answer Jason. I got the solution. It is most useful for multiple conditions with multiple fields/columns…
Thank you so much Jason…
Thanks for the fantastic post! I have one question. Sometimes when I am using a two criteria formula with index and match just as you described. I want to return a “0” or blank if the result is #N/A.
How do I change the formula to return 0 or something else when both criteria are not met?
Hi,
the formula will look originally to something like that:
={INDEX(Range,MATCH(1,(criteria1)*(criteria2)),0),Col num in Index range)}
Just put your IFERROR like that:
={IFERROR(INDEX(…,MATCH(…),Col Index range),Value if error)}
Value if error can be either a number or a text (that you would need to put in quotes).
I hope this will help you if you haven’t found the way to do it yet.
I just learned the Index(Match()) with multiple criteria following this great tutorial. concise, clear and well written.
Cheers.
fantastic, thanks
Hi,
I found your formula very useful. Can you please explain its working to me?
Thanks!
just iferror fuction
Hi Randy,
Glad you like the tutorial! If you want to catch errors like #N/A, you need to wrap the INDEX(MATCH()) in an IFERROR() function. This lets you specify the behavior when you have an error. For more details, check out this guide on Excel error handling.
I’m trying to add the IFERROR to an index/match function and can’t seem to get it right. Can you be specific about how to add this?
Hi Kelly,
You might find my guide on error handling useful… Specifically, the section on IFERROR and Common Error Handling Techniques. You can find it here.
Great post, and almost fixes my problem, I’m just wondering how you can use this if you have multiple similar instances of the searched criteria (say two people with the same first and last name) and therefore multiple outcomes exist. Is there a way to get excel to look up the 1st, 2nd, 3rd etc. of each combined instance so I can put them in a row. I’ve attached an example of what I’m trying to do. And thanks for any help you can provide
Thijs I’m looking to something similar, can you show me the array formulas you used?
Hi Thijs,
Depending on what you’re trying to do, you might find How to Combine Data from Multiple Rows into One Cell or Extract a List of Values Filtered by Criteria with Sub-Arrays useful.
Hey Andrew,
Thanks for the reply, I actually figured it out on my own, I just used Vlookup but simply combined the two search criterea and the two rows where to find it using an & function That way I could simply ask it to find outcome 1 to 15 and get my results.
Thanks, helped alot. Although I have a table of around 7000 and this method takes alot of time. What would be a faster way to do this. I think it is slow because it has to create an array for each cell. It does about 5.8 every sec. = 20 mins of waiting. Just in case it is relevant, no other calculations are on the sheet, and I am using 3 critiera.
Hi Devin,
Because the INDEX/MATCH has to compare each criteria against the entire table, lots of rows and lots of criteria makes the number of necessary calculations grow exponentially. Try to think of ways to pre-calculate some of the criteria to reduce the total number in the INDEX/MATCH or search a sub-set of the data where possible. Also, if you are working with the table often, it may be worth it to shut off automatic re-calculation and only re-run the 20 minute session when you are finished making changes.
Good luck!
Andrew
I’m looking for a formula that look a 3 criteria, item, size and month..I wrote the formula many different ways but can’t seem to get it right can you pls advise. thanks ahead for your help
Hi Chris,
The basic format for a 3-criteria INDEX/MATCH would be as follows:
=INDEX(TableRange,MATCH(1,(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2)*(CriteriaRange3=Criteria3),0),ReturnColumnNumber)
If you have more specific difficulties, I’d suggest you submit a question at one of the forums available on my Web Resources page.
Good luck!
Andrew
Thank you so very much for posting this. I was able to get both the two and three criteria index/match combination to work pretty consistently (a few that didn’t work, however – posting my example in the forum, thank you for the link).
Again, thank you from all of us. The Index/Match combination is impressively powerful and the fact that you don’t need your table in any certain order is phenomenal.
THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!
I’ve adapted your formula to save me tons of manual filtering…
This worked perfectly once I put it in as an array formula. Thanks!
Hi Andrew,
I have the same issue as Chris does (matching 3 criteria), plus I need to match a few columns of data, and I need to pull it from one tab into another tab. Is that possible?
Rachel
Thanks Andrew! If this works, it will save myself and my team hours of pressured manual excel labor.
Thanks for taking the time to write this fantastic explanation for using INDEX and MATCH for multiple criteria. I’ve actually been using the MATCH part for a year or so, but I had no idea what the 1 aspect of it was about. Yours is the first article that explained the logic behind it. I never comment on blog posts, but I had to for this – it was excellent!
Much appreciated!
Thanks for this great tutorial. I finally understand how to use the Index + Match function and was able to use it in a complex spreadsheet for work. In the end, I had to transpose the data from a vertically structured table to a horizontally structured one. Is there a way to use Index + Match for data that is structured from top to bottom? Either way, a great tutorial and well written blog. Keep up the good work!
Thanks! Great Stuff!
How many criteria can I fit in using this method?
Best regards!
Hi Jonas –
You can use INDEX/MATCH vertically or horizontally… You just need to use the MATCH statement in either the row number or column number field. Syntax for INDEX is as follows: =INDEX(array, row_num, [col_num])
Hi James –
As many criteria as you want… I warn, however, that each additional criteria adds computation time. If you do more than two, things start slowing down exponentially. It may be easier to concatenate multiple criteria into one to save on computation.
Thank you so much for the step by step instructions. I can’t tell you how much time this saved me!
Thank you Andrew! Very nice guide and extremely useful!
“If this process doesn’t make sense to you, that’s okay. Just plug the new MATCH function into your INDEX/MATCH formula:”,
hahahahaha! really enjoyed when I read it!
This almost does exactly what i’m looking to do. I just want to know is it possible to generate more results. so If i wanted to generate a list from my table of everyone with a certain qualification.
Hi Dustin,
I think you might find this tutorial useful:
https://exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/
Thank you – this is a big help. I was able to do the formula with 3 variables. My only issue is I’d like to copy this formula down an entire column of 100K+ line items. I am unable to just click the bottom right corner because I get a #REF! error. The formula copies with “#REF!” where the match range is supposed to be. The only way I can get it to work is hit the CTRL+SHIFT+ENTER in each cell, but I cannot do this 100K+ times. Any simple way to copy this formula down an entire column?
Hi Katie,
Congratulations on getting the formula up and running! Array formulas (the ones you enter with CTRL+SHIFT+ENTER) are a bit picky about how you copy them. You can select the cell with the entered formula and drag down with the bottom right corner to copy the formula down. You can also copy and paste the formula into other cells, but you must exclude the original cell from the paste selection or Excel will give an error.
As an aside, I will caution you that a 3-criteria INDEX/MATCH applied across 100k cells will likely take a very long time to compute (on the order of hours). Be sure to save your work before copying the formula down and plan to do so when you are able to leave Excel alone to process for several hours.
Good luck!
Andrew
Hi Andrew,
This is a very nice article/tutorial! I have found it can be used in differnet circumstances, however I was wondering whether you can provide further explanation in regards to a particualr situation. I am trying to produce a report that checks for a name in table (that is repeated multiple times) and selects the the most recent entry (each entry has a different date in a different column). I ma breaking my head trying to use your approach but not sure how it could make it work. Could you cast some light on this?
Many thanks!
Hi Alberto,
Thanks for the feedback! This method should always return the first match it finds, so if you pre-sort your list so that newer entries appear at the top, the lookup should work the way you want.
Good luck!
A
Hi, I wondered if there was a way to do this within the formula using the MAX command somehow?
I am essentially trying to achieve the same thing, but don’t want to have someone to go in and re-sort the data in the sheet all the time as the INDEX/MATCH lookup is being used in a different sheet to the source data. Is it possible?
Hi,
I think there is an easy way to do it (but you need a new column in your data sheet)
Please check below.
i merged the cells that I want to use in criteria and put them in column C together (=A6&” “&B6)
Then used this formula to match it. =VLOOKUP(G6&” “&H6,$C$6:$D$11,2,FALSE)
That’s it. You can even create as much criteria as you want.
Regards
Dear Unal Ji,
Thanks a lot for superb guidelines.
=INDEX(G12:G16,MATCH(B7,C12:C16,0))
This How to saved me about 5 days woth of work… its is awesome. Thanks a bunch.
Really very helpful, thanks lot.
When I download your example in Excel 2007, it doesn’t return an answer in cell C2 but rather #VALUE!
Any ideas why? I didn’t change anything.
The formula in C2 of the example worksheet is an array formula. These were available as early as Excel 2003. If there is a #VALUE! error in cell C2, the formula has probably been re-entered by pressing only ENTER instead of CTRL+SHIFT+ENTER. If you can’t see the {} curly brackets around the =INDEX() command in the formula box, you need to click in the formula box and re-enter the array formula using CTRL+SHIFT+ENTER.
Great that works!
Reply to: Unal comment below. Thank you very much. This formula has saved my life. This is genius thanks again!!!!
Hi,
I think there is an easy way to do it (but you need a new column in your data sheet)
Please check below.
i merged the cells that I want to use in criteria and put them in column C together (=A6&” “&B6)
Then used this formula to match it. =VLOOKUP(G6&” “&H6,$C$6:$D$11,2,FALSE)
That’s it. You can even create as much criteria as you want.
Regards
This is very cool. Thanks for sharing. I had good luck with ctrl R and ctrl D to copy down and right. I didn’t know about IFERROR. I routinely use =IF(ISERROR(formula),””,formula) to get blanks instead of #N/A or #VALUE. Cheers.
Thanks very much for sharing your knowledge. This worked perfectly for me!
I have a different (and maybe easier) challenge. I’m trying to do the sum of all the values that meet a criteria i specify. For example, I have a 2-column table/array that consists of months and their values. I have several rows containing Jan 2015 and want to add up all values for Jan 2015. How do i do this?
Sounds like a job for SUMIF() or SUMIFS() to me…
Hello,
I am having a problem getting the formula to work I keep getting a result of N/A even though I know there is a match.
Here is my formula
=INDEX(Sheet1!$B$6:AD$32,MATCH(1,(Sheet1!$B$6:$AD$32=Sheet2!$A5)*(Sheet1!$C$6:$AD$32=Sheet2!H$2),0),Sheet3!$D$3)
Check to make sure the formula is entered with CTRL+ALT+ENTER and not just ENTER. You’ll see curly braces {} around the formula if it is done correctly.
Hi Andrew, I’m having a similar issue. When I drag the formula into the rest of the column, it changes the array I’m searching through. I want it to search through. In my first cell the formula is {=INDEX(Sheet2!A1:C61,MATCH(1,(Sheet2!A1:A61=DATA!C2)*(Sheet2!B1:B61=DATA!D2),0),3)} but when I drag into the next cell it changes the formula to {=INDEX(Sheet2!A2:C62,MATCH(1,(Sheet2!A2:A62=DATA!C3)*(Sheet2!B2:B62=DATA!D3),0),3)}
How can I keep that fixed?
All you need to do is lock the row references. Sheet2!A1:A61 becomes Sheet2!$A$1:$A$61.
Thanks Andrew, the formula is working fine, there is a small issue, when Match does not find the value its giving me #N/A, will you please tell me how I can use IFERROR,, ISNA or some other function to avoid this issue.
{=INDEX(Order!$A$2:$C$1000,MATCH(1,(Order!$A$2:$A$1000=K$1)*(Order!$B$2:$B$1000=Production!$A3),0),3)}
Hi Arif,
The IFERROR() formula is pretty straightforward. Just use the form IFERROR(original_formula, formula_if_error). You’ll still need to enter with CTRL+SHIFT+ENTER. For more error handling options, you might like the Definitive Guide to Excel Error Types and Error Handling.
Hi Andrew,
Thanks for this post. Please, letting me to know if it’s possible to avoid the array option. It works for one cell (one record) at the time. I wanna use a formula for many records and drag the formula to complete the search. Much obliged,
You should still be able to drag down the formula after you enter it as an array. Another option is to concatenate all your criteria into a single string to avoid the “multiple criteria” INDEX/MATCH all together…
Thanks Andrew. Can you please show me an example fot the single string option? Thanks a lot!
Hi Wilder,
Long time coming, but here is a tutorial on how to get Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE that should help!
How’d you get away with the implied if/then? In my own implementation I have to add IF() functions to get the Boolean values.
Oddly, your example works when I download it. The only operational difference between yours and mine is that I’m referencing values in a separate workbook.
Hi,
Thanks for sharing this very useful formula. I am using this to reference data from two separate Tables. I attempted to use the Table name and Table Columns as reference in the Match formula. It looked like this:
{=Match(1,(Inventory[Page ID]=[@Page])*(Inventory[Language]=[@Language]),0)}
However, this did not work. I had to use absolute cell references – e.g., A2:A100. Any idea why this would be? Perhaps I am just referencing incorrectly?
Cheers,
Adam
Hi Adam,
Data Tables in Excel work on internal logic that lets you easily duplicate formulas all the way down the rows. Your reference @Page is referring to a relative page in the row of the table. It is likely not interpreting this correctly for the MATCH, which generally works on absolute row references. There may be a way to use table references in INDEX/MATCH calls, but I have not explored the topic myself. Sounds like you got it to work, though!
Andrew
hi Andrew,
thanks for this great tutorial.
However, it seems that the INDEX/ MATCH combination plus Ctrl+Shift+Enter is too computationally intensive. Excel says it ‘ran out resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.’ This seems to happen when there are already a number of formulas in the spreadsheet. Unfortunately this makes the combi un-usable.
Thanks for this great tutorial – it is exactly what I needed, and is the most well-written and easy to follow post of its kind that I’ve seen. Keep up the good work!
Very useful details for cell comparision
Thanks a lot for posting this.
It really helped me solve a problem that I was struggling with.
This is a great article but I have a slightly different issue. I have two sheets of data that have three of the same columns that I want to compare the row fields. If there is not a match from one sheet to another then I need to generate a report of those differences or at least hightlight the non-matches. Any ideas on how to do that? Thanks!
You may find my article on Sub-Arrays useful: Extract a List of Values Filtered by Criteria with Sub-Arrays
This is the best solution ever! Thank you. Thank you.
Thanks for the great tutorial Andrew. Whenever im faced with such problems i usually just VLOOKUP and simply combine the criteria in a cell and use that as the start of my table array. This method however mean you have to add another column to the mix. =VLOOKUP($B$2&$C$2,$A$5:$I$1159,9,0)
Thanks for this great tutorial. Works almost perfectly for my application. The only issue I have is that one of the criteria I have is case sensitive. Is there any way to make the match formula case sensitive?
Hey Andrew,
Thanks for a great tutorial. You solved my problem with Index and Match. The formula combined with =IFERROR works like a charm. I had 3 set of criteria to be looked before giving the value. Thanks for making this work….
Rgds,
JK
hi,
Just wanted to know how to get nth matched value from the below formula.(Match function returns multiple 1’s and want nth 1)
=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)
Thanks in advance.
I am still getting #N/A formula. I have checked several times to make sure that it is an array. I am using Excel 2007. Do I need to include the iferror?
hi,
Just wanted to know how to get data matched with code no and month so that the result will be 25000 (1101 & Apr -14)
Use vlookup & match function
Hi there;
I’m having a similar issue as Terry; it’s funny, because if I click on the Fx symbol, it shows the result I want but the cell still says #N/A. I’ve clicked on the symbol next to it to analyze it and it says that first part of the MATCH function (in the formula given, it’s the C4:C1159 range) is the problem but I cannot determine why.
Any thoughts, or ideas? I would love to be able to do this without resorting to concatenating the two fields together to create the look-up value!
Thanks in advance!
Hi,
Once, you enter the formula in the cell, dont press enter. instead Press CTRL+SHIFT+ENTER to tell Excel that it is an array formula. It will only work then. Else it will keep giving #N/A.
Hope this was useful.
Jay… thank you so much! We’ve been concatenating the value in the two fields and then doing VLOOKUP… which isn’t exactly ideal. Now that I could get this to work, I will share with my coworkers!
this is great info, thanks sooo much!! this is exactly what I needed.
I did a test on this and it worked perfectly, but all my data was on 1 sheet.
then I tried to apply it to my real workbook, which has data on 2 sheets, and received the error ‘the formula you typed contains an error’. here is my real formula:
=index(‘Line Detail’!A1:K546,match(1,’Line Detail’!K1:K546=2016)*(‘Line Detail’!B1:B546=’Summary Original’!A3),0),11)
the error seems to come from the ’11’ at the end of the formula. I guess it doesn’t know that the 11 refers to the 11th column in my specified table on my ‘Line Detail’ page. i tried putting ‘Line Detail’ before the 11 and that didn’t work.
any helpful hints? thanks in advance!
Disregard the post I just posted! I left out a “(“. doh! (it works on separate sheets).
Happens to the best of us!
If you want to know more about “Using Vlookup And Isna Functions To Find Matching Values in Different Sheets”, check this link ……..
http://www.exceltip.com/lookup-formulas/using-vlookup-and-isna-functions-to-find-matching-values-in-different-sheets.html
I generally don’t reply to discussions, but this tip and the instructions were incredibly well explained and laid out. This has helped me increase report efficiency in other workbooks and KPIs. Thank you!
I’m looking at the performance impact of a two criteria index match versus using a sumif formula. I’ve been tasked with improving the update speed of a spreadsheet that has about 18,000 rows and 15 columns. (That’s about 360,000 cells that have this formula). The table is more a matrix so I have to have a formula for each row and column to find the value where they meet. Right now we are using sumif’s and it takes about 30 minutes to refresh. Do you think a two criteria index match would have any performance boost in this instance?
Thanks for your advice. This was a great guide.
If speed is what you are looking for, INDEX/MATCH with multiple criteria might not be the best approach… I’ll refer you to the article: Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
You may need to make an intermediate matrix of all the criteria in a hidden tab to get the full efficiency of the concatenation approach, but it will definitely be faster than standard INDEX/MATCH with two criteria, as it only has to do one compare.
Need to do a double approximation for a 3 column table and efforts to use > < or -1,0,1 as match types do not seem to work?
gauge width price
0.018 48 $3.00 {=INDEX(B4:D54,MATCH(1,(B4:B54=A2)*(C4:C54=B2),0),3)}
0.019 49 #N/A
PT Gauge Width $/cwt
CR 0.015 52.5 $4.00
CR 0.015 61.5 $4.00
CR 0.018 36 $6.00
CR 0.018 42 $4.50
CR 0.018 48 $3.00
CR 0.018 52.5 $3.00
CR 0.018 61.5 $3.00
CR 0.022 36 $4.00
How to find the value of row 3 which criteria base on the first and second row?
thank you so much,it’s very useful for the multiple criteria index+match…….
Wow!!! Awesome, thank you. This has saved me a bunch of time!!
Hello —
I would appreciate some help with this formula
I have this table that is used for the look up:
Fiscal Month Start Date End Date
Jun 5/26/2014 6/29/2014
July 6/30/2014 7/27/2014
Aug 7/28/2014 8/24/2014
Sep 8/25/2014 9/28/2014
Oct 9/29/2014 10/26/2014
Nov 10/27/2014 11/23/2014
Dec 11/24/2014 12/28/2014
Jan 12/29/2014 1/25/2015
Feb 1/26/2015 2/22/2015
Mar 2/23/2015 3/29/2015
Apr 3/30/2015 4/26/2015
May 4/27/2015 5/24/2015
For the dates below, I want it to populate the month above if the date is in between the start and end dates above
My results show May for all rows rather than the correct month
Here is the formula that I used:
=INDEX( Check!$A$9:$A$20, MATCH(1, (A10>=Check!$B$9:$B$20) * (A10<=Check!$C$9:$C$20)),0)
Date Result Expected result
6/11/2014 May Jun
7/15/2014 May July
8/18/2014 May Aug
very very good article. well explained. thump up
I have a master spreadsheet with columns A through U and data on rows 2 through 900. I would like to pull all column/row data based on one criteria ( which would be found in column O and one of 5 options) and display results with the same column headings as the master but in another tab of the workbook. Can you please tell me if this is possible?
Great post. Thank you Andrew.
I thought I’d share what I was working on. I needed to create a table to look up commission rates where the rate could vary by day and by partner, but not for a uniform list. For example, for one partner, the rate may change on Oct, 1, where another one never changes and another one went up for a two month period and then changed to another rate.
In my source data I added in an effective date for each change in rates which was the LAST effective date of the rates. The base effective rate was 10 years in the future.
The in the index formula I looked up for values where the date was >= the date I was searching for.
Worked very well, just the array is heavy on calculations.
Happy to share if anyone wants any more info
Avraham
Thanks for the example. I have a question on just the MATCH formula. As per my understanding, MATCH formula only takes one look-up value, can I specify multiple look-up values where if one of the values is found, I would like it to return the result. Example: I have a row made up of a lot of empty cells, some cells marked as a, some as c, same as d, some as 4, some as 7. I want the formula to return the first relative position in a range where either of 4 or 7 are found while looking up.
many thanks!
This just changed my life. Thanks so much 🙂
Thank you! This saved me a LOT of time and effort messing around with VBA and writing a macro. It took me a bit to figure out that you had to match “1” (I thought it was looking for the number 1 in a column to begin with), and then I discovered that you cannot use “TRUE” in its place if you want to use this technique. Those two pieces of information are critical to making this work. I’ve now successfully matched my data on three criteria, and it works beautifully.
Hi
In a column like c1,c2.c3….. i have paragraphs and in d1,d2.d3… i need my output , from that paragraph i need to filter only the words that matches names like joseph and anna and lawrence, is there any formula for it…..
I’m using this formula and entering Ctrl + Shift + Enter and it’s returning the 1st value in the column chosen, not the value that matches both criteria. …can you please help???
=INDEX(WorkingMTD,MATCH(1,(‘NYC Working MTD’!A26:A432=A3)*(‘NYC Working MTD’!D26:D432=$B$1),0),5)
Hi,
Can anyone help me in getting the SR No if i enter the date and country. For example if i enter Origin country as Croatia and date as 01 JUL14, i should get the corresponding SR NO as 18.
Hi Andrew I loved the post thanks very much it solved one of the problems I had been banging my head on. Thankyou Thankyou!
I feel that it’s close to solving a second one as well but I can’t seem to make that final logic step. I have given an example of my data set the problem I have a hard time wrapping my head around is that my range for “blue” for example could be anywhere from 1-30 lines.
Here is the issue. I need to return the largest value for each grouping
DATA SET Expected Return
blue 4 Blue 96
blue 16
blue 42
blue 96
Green 12
Green 18 Green 18
Orange 3
Orange 47 Orange 47
add 6000+ more lines
Thanks in Advance for any help your able to offer
Wowwww Such a wonderful function. Thanks for explaining and bringing it to us. 🙂
Hi, thanks for the fantastic explanation. A couple of questions:
– My formula is returning the value BELOW the one that I am expecting. What am I doing wrong? The criteria in my formula are definitely correct.
– I am returning a value based on 2 criteria. Is this the most efficient way? My spreadsheet is going to contain a lot of formulas so I’m worried about performance. Would it be better to use a sum if array formula? Vlookup?
Many thanks
My god man … it is soooo helpful!
The match function is able to sort through rows of data and indicate back to the index function which row to pull from, is there a way to use the match formula to return column numbers as well? Or is there a separate formula that can return column numbers based off of multiple criteria?
Please can anyone advise me with regards to this formula i’m finding to figure out.
Basically, i’m creating a timesheet tracker report. Each week you will be able to identify who’s timesheet has been submitted, unsubmitted and missing. I’ve managed to do a formula based on each of these categories however i’m unable to bring the dates. The report will be run on a weekly basis therefore the dates will change in each column. Can anyone advise?
I need to create a formula in workbook 1 that looks in workbook 2 for a store number, the date, and gives me a total cost. Example. I need in the yellow to look for store 4469 on 2/10/15 and tell me the total cost spent on that date. If correct the cell will total $700
try this..
my own solution hope you like it..
i put extra cells to merge the date and store no. to call it as one..
Thank you very much.
It is very innovative and very useful.
Full marks and kudos to you.
Hi Andrew –
I’m not sure if this has been covered, but I am trying to use INDEX MATCH to find data that doesn’t necessarily match 1:1 by name using multiple criteria.
Essentially, I have two sheets and I want to compare columns A B C on both sheets where A=A, B=B, etc. The problem is that the text names do not match exactly with a certain amount of variation. The only column that should match 1:1 is column C. So, I want to use either a wildcard or character match for both A to A and B to B with C = C 1:1 and returning D.
I was able to write a formula that uses a wildcard for A:A, but I can’t seem to find a way to use it for B:B and add C:C (as the 1:1) to compare all 3 elements and return C.
This is my formula: =INDEX(SHEET1!$D$2:$D$406, MATCH(“*”&LEFT(SHEET2!A2,5)&”*”, IF(SHEET1!$B$2:$B$406=SHEET2!B2,SHEET1!$A$2:$A$406),0))
thank you very very very much…
10 out of 10…
I have an excel with items listed in column a… across the top are order numbers and below each order number it has quantities. I want to be able to pull all PO’s and quantities under each for each item.. However, the item may have more than one po. How can I pull each item, with multiple po’s and quantities on one line?
I tried to copy paste your example, and Ctrl/shift+Enter . But as you can see in my picture I get this error in formula. Maybe I got the wrong Excel, using, Excel Pro 2010 ver. 14.0.6106.5005.
I need to have a list of the top five items in my list which I have created but I have two fields with the same number. I need to look for the header for that value but, the fields that have the same value the index matched the headers the same. All I want is to go to the next matching value and match the header. Can anyone help?
=INDEX($B$1:$B$150,MATCH(L13,$C$1:$C$150,0))
Please help. Currently this process has taken over a week and I am hoping someone can simplify this. I have tried INDEX/MATCH and cant figure it out.
I have a listing of employees who each have a current phone bill amount.
EID
Amount of Phone Bill
I have a spreadsheet that provides me a listing of the Employees
EID
Job Name
Job Hours
% of Total Hours
I need to merge these sheets together so I have 1 working sheet but my issue is an employee can charge several jobs and I can only get the 1st job to appear. Will excel know to add additional lines based on the number of jobs an employee charges?
Any help would be greatly appreciated.
I tried this formula so many times with unsuccessful result.
After I follow your tactic, it’s work!!!
Billion Thanks
Hi, Thanks for your guidance. This method solved my problem.
TQVM
whoaaa!!it works!!
many thanks for this great tutorial.finally understand how to use the index n match function.Keep up the good work!!
I’m trying to use INDEX and MATCH to find a value based on multiple criteria. I’ve got it working for regular tables.
Except I want to find the value in a pivot table. Do you know if this is possible? And if so, what my array selection should be?
I got a SUMPRODUCT to work in one workbook A, but the same expression is not working in another workbook B and I can’t see why.
In both workbooks, the issue is I need to lookup a piece of information based on one identifier AND “the most recent date” for that identifier.
So, in workbook A, I’m looking up a tax rate based on province and the latest date in a table of rates sorted by province and date and the following formula works perfectly (because tax rates change periodically but past dates should continue to be calculated against the applicable rate at the time of the transaction):
SUMPRODUCT(([ProvinceList]=[province]*([DateList]<=[date]),([CorrespondingRatefromRateList]))
In workbook B, I need a frame number based on unit and the most recent frame installation and the same formula as above, applied to this, does not work:
SUMPRODUCT(([UnitList]=[unit])*([DateList]<=[date]),([CorrespondingComponentIDfromComponentList]))
I'd just prefer to use SUMPRODUCT rather than array formulas where possible, and I also want to understand why the identical construction of a formula works in one situation but not in a similar situation in another workbook.
Any help is much appreciated.
Cheers.
Great tutorial, useful technique. Problem: I need a logical OR for the two criteria instead of an AND. Thus, if(criteria1 OR criteria2) then value in return column. Given a technician’s name or ID number, return the department they’re in. The Excel OR function returns a boolean, and I’m trying to work through from that to a return value based on the department column, but not having much luck. I get the feeling that I’m overcomplicating the solution. Advice?
Hi
It’s great tutorial. I’d like to ask for some help.
I would like to use multiple criteria but one should be 100% match and second should be just partial match. So for example Last name must match, post code must match but first name must match on first letter from check cell and first letter on check cells. (or first two letters)
I’ve created that:
=INDEX(A2:A43,MATCH(“*”&LEFT(H5,2)&”*”,B2:B45,0))
but I can’t transfer it into multiple criteria:
=INDEX(A2:C43,MATCH(1,(C2:C41=G1)*(B2:B45=”*”&LEFT(H5,2)&”*”),0),1)
something is wrong
Then I’ve tried this one and it seems to be OK but I’m not sure if it works right.
=INDEX(A2:C43,MATCH(1,(C2:C41=G1)*(B2:B45=”*”&LEFT(H1,2)&”*”),0),1)
Thank you for any suggestion
Hi, worked great but I have one problem. In my “lookup array” I have more than one set of the same “lookup values”. Which means the formula is going to find the first set going down the list. Completely ignoring the other sets of the same “lookup values.” Is there a way around this, without having to add any additional criteria to my lookup array???
Hi Andrew,
I have a problem with my formula, but I can’t find where. The result is either #Value! (if source and result are in the same sheet):
=INDEX($A$5:$G$10,MATCH(1,($A$5:$A$10=G2)*($D$5:$D$10=H1),0),6)
or #N/A (if source is in a different sheet than the result).
=INDEX(Models!$A$5:$G$10,MATCH(1,(Models!$A$5:$A$10=B7)*(Models!$D$5:$D$10=W3),0),6)
My range is a pivot table, which didn’t cause a problem with vlookup before.
I am working in an xlsx file.
Ctrl+Shift+Enter didn’t help either.
Attached is a png of my source table.
Many thanks for your help,
Melinda
This is great info. Can you help me resolve an error? This is my formula:
=INDEX(Sheet1!1:1048576,MATCH(1,(Sheet1!E:E=A2)*(Sheet1!B:B=B2),0),8)
This formula is used in one sheet to go to another sheet (Sheet1), find a row where the data in Sheet 1’s column E matches A2 and the data in Sheet1’s column B matches B2, then fills the cell the data from the 8th column of the matching row from Sheet1. However, I am getting #N/A instead.
Thanks a lot!
Solved my problem perfectly. Thanks a lot!
A hearty Thank You! for your explanation that transitioned me mentally from VLOOKUP to the equivalent INDEX,MATCH to the multi-criteria INDEX,MATCH. I’m trying to simplify chart building to convey the cost of annual program releases segregated by their included project subreleases. The previous author was happy with pointing to cells in a “chart creation table” to data in a source table, but turning the source table into a range and selecting within the “chart creation table” based on data in two cells in a row plus a variable column for the year gives me a “universal” function that I can copy into whatever cells need to be populated.
Building complex charts in Excel is a feat in itself, what with the dummy columns to put space betwen series to make them easier to visualize, so your explanation here lets me simplify at least the population of the cells in the chart that the table looks at.
Alan
This post was so helpful! #mindblown
I am trying to find an average of items sold on Monday, Tuesday, Wednesday, etc.
So basically A5 in attached sheet would find all Mondays (from columns labeled “Monday Total”) and find all values in Row 5 for only of Monday columns, and return an average in A5.
In B5, there would be an average of all numbers from “Tuesday Total” column, an so on.
I am trying to track sold items. There is daily tracking (from Column J and on..) and then there is an average items sold for all Mondays, Tuesdays, Wednesdays, etc. so it would give me better idea of how many items are being sold on an average on Monday, Tuesday, Wednesday, etc.
I hope my description is not too confusing.
Appreciate the help in advance! Thank you!
try this..hope it can help
..
Hi
I know this must be basic but I am stuck can you help please
I am trying to get my formula to look at different columns but one column has **PC**12345678 in it.
I have my look up data which are numbers then I have a shelf number and then I have the number I have scanned in to the spread sheet.
My problem is I can’t get the vlookup to see passed the ( **pc**) and only look at the number can you help.
thank you for your help
Justin
Hi Andrew,
Thank you for your informative article on Index and Match.
This is the second article I am reading on Index and Match but I was able to follow and generate the correct answer this time.
Your explanations were pretty clear.
Wendy
Andrew, I am awed by how well you describe this complicated scenario in Excel. Thank you for sharing.
Very interesting post – however I found this to be quite a slow method than I expected.
In the past I have used this method
=IFERROR(SUMIFS(‘May15′!$c2:$c31001,’May15′!$A2:$A31001,$A7,’May15’!$F2:$F31001,T$2),0)
In this example I sum a value where column A is a changing value and column F is a fixed value (in this case a date)
=IFERROR(SUMIFS(‘May15′!$c2:$c31001,’May15′!$A2:$A31001,”East”,’May15’!$F2:$F31001,”01/05/2015″),0)
This is over 50% faster than
=IFERROR(INDEX(‘May15’!$A2:$E31001,MATCH(1,(‘May15’!$A2:$a31001=A7)*(‘May15’!$f2:$F31001)=X$2),0),3),0)
but gives the same result.
This does surprise me because I have always found INDEX & MATCH to be a lot faster.
It might be down to how ‘May15’ is ordered so I swapped the two expressions around
=IFERROR(INDEX(‘May15’!$A2:$E31001,MATCH(1,(‘May15’!$f2:$F31001)=X$2)*(‘May15’!$A2:$a31001=A7),0),3),0)
so that the date was first – this column is in date order
This made the INDEX & MATCH version 40% faster – so a result.
But it does show that the order of the lookup table is important, if this is not predictable then you may want to use a different method
Thank you so much. I’ve seen similar formulas used for various tasks I’ve needed but I haven’t been able to find a post where someone explains what values are being returned. That’s the information I needed in order to know how to modify the formula to fit a variety of needs. This is the first time I’ve fully understood the Index and Match functions so I can use them on my own in the future.
the array formula was fantastic and very helpful. However, I was hoping to set another filter to this array.
I have a file with 600,000 rows of data. I was able to use your formula to pull back the data I needed based on 3 criteria points. from this point I want to pull back only the top 4 or 5 options. It currently pulls back 30 – 40 rows from the 600K rows. I was trying string in the large function but I can’t figure out the proper formula.
Any suggestions?
Great tutorial, I put it to what I needed – and it worked! Thanks
That was super cool ,thanks !
I’ve been looking for a simple way to do this. Great explanation, thanks.
Fantastic!!!! Took me a while to understand and implement this (DO NOT FORGET to hit Ctrl+Shift+Enter at the end of your formula PLEASE, AS INSTRUCTED HERE!!!) but eventually worked like a charm. I had two tables in excel that I had to join by Purchase Order Prefix, Purchase order Number, Page Nbr, and Line Nbr to link up the cost per unit with nbr of units and multiply the two together. This INDEX(….MATCH …)) worked perfectly!!! 🙂 THANK YOU!!!
This is orgasmic!
Great Tutorial!!
Question – The index match formula works but before any data is entered into my chart it automatically states table 1 seat 1 in the output field? I would like it to be blank like the ticket number and table captain field?
Thank you.
Hi
Thanks a lot for the help, really makes life easier,
wondering can we do something similar with HLOOKUP
Hi Loai! INDEX/MATCH can be used to imitate HLOOKUP or VLOOKUP equally. Just look at the syntax of the INDEX statement to adjust your queries accordingly…
Thanks a Lot Andrew, working great
Hello
I need some help
I want to extract data, if it meets only one criteria from first column (C) or second column (E), into one row, but, if it meets two criteria from two different columns – into two different rows.
Thank You
Thank you SOOOOOOOOOOOOOOO much! I work in a manufacturing environment and this tiny little trick is going to save so much time and money and error proofing and whatnot. Thank you so much!!!!!!!!!!!!!!!!!
So what exactly is the difference between this method and sumifs?
Hi Andrew,
thanks for this article, it’s very useful and explained with extreme clarity!
I have one more question for you, though, as I have to do something more: I need also to sum the values found with this double comparison method.
To better explain: I have values in one column, corresponding to countries and purchase orders. I need to sum and report in a different tab all what was invoiced per each country, based on the purchase order.
With the INDEX + MATCH formula I manage to get only the first entry of the column where the double criteria PO + Country corresponds. I believe I should add a SUMIF formula before this one to have it working as I need it, however I’d need some help to build it because any of my attempts works.
Any suggestion?
Hi there, I’m using this formula for more than 50,000 lines.
Is there any way that I don’t have press (Ctrl+Shift+Enter) for each lines ?
Or there is a shortcut that I’m not aware of ?
Once you have used CTRL+SHIFT+ENTER for the first row, you can drag the formula down and it will maintain it’s “array formula” status.
Thanks Andrew, you described this topic in easy to understand ways that I understood with ease. I will guarantee that come Monday morning I’ll be puting to use what you just taught me, within minutes of arriving at the office. I’m very grateful you posted this.
Hi,
I want to extract all the information from one sheet to another sheet where a field criteria is met. at present if I use a vlookup it only takes the first and ignores the rest. How do I get around this.
I owe you my total gratitude for explaining this! I’ve been using VLookup for ages but I’ve been at a loss at how to use more than one criteria. This is the solution to my problem! thanks so much!
So Cool! CTRL, SHIFT, ENTER made the difference! Failed to realize that was the key to my problem. Now it works! Thank you for sharing!
Really helpful, save so much time! Thank you very much
Thanks Andrew, this is really helpful! Now your website is my go to place!
Also the explanation is clear once you actually start reading:)
I’m trying to use this formula to return multiple values. So it works for the first entry it finds when the two criteria are met, but once I drag down it just keeps returning the same results. What do I modify to have to keep searching through and pulling all cells that meet the criteria?
Hi Kenny!
I think you’d find this article on how to Extract a List of Values Filtered by Criteria with Sub-Arrays useful.
Andrew
Thank you so much!.. this was amazing and worked perfectly. Saved the day!
Hi, thank you very much for the detail and clear instruction.
I would like to use this great method to lookup values from criteria of both numbers, but excel return #N/A. I have tried to change the ‘category’ in ‘format cell’ but still obtaining the same output.
It works perfectly if one of the criteria is not number though.
What have I missed out?
Hi
I have a little question.
I made a little selection, like this:
{=IFERROR(INDEX(Calendar!B$7:B$1005;SMALL(IF((Calendar!$A$7:$A$1005)=$N$1;ROW(Calendar!B$7:B$1005)-ROW(Calendar!B$7)+1);ROW(1:1)));””)}
It works perfectly!
But … I have another question.
Is it possible that when I striketrough my source cell in the tab “Calendar” that it’s also strikedtrough in the destination cell?
Thanks in advanced for your feedback!
Kind regards,
Ans.
It didn’t work for me
I have property references against multiple job numbers and each job number has multiple values.
so, I want to match a job number & the value I already have against the table explained above. how would this work?
Please share if you find the answer for this. This is also my problem. 🙂
Your post (nor the one above it) does not give enough details to describe your situation. If you email me directly, I might be able to help. Feel free to send me your spreadsheet. You can make up fake data in it if you wish, or send me the actual data. Once I solve the problem for you, I will delete all traces of it on my system and never use it, publish it anywhere, or send it to anyone else. byoung@wcanalytics.com.
Thank you, this was extremely helpful!
Hello Andrew,
I just wanted to share my appreciation for this tutorial, and the example spreadsheet. I spent hours on other website trying to figure out how to do this, but this example finally helped me to achieve what I wanted to do.
– Adam
This is great! – I was wondering if INDEX + Match can be incorporated into VBA Excel Macro code?
Hey
It s a nice article, was trying to use the above using match conditions across 2 sheets, did not seem to work for me.
Any suggestions
Thank you so much! You just saved my day. Words can’t express my appreciation.
Hi,
Would it be possible to add a tolerance (say 5% plus or minus) to the formula below:
{=INDEX(R3:T8189,MATCH(1, (R3:R8189=F12) * (S3:S8189=G12), 0), 3)}
So that the vales I enter don’t have to be an EXACT match?
Cheers,
Dan
Hello,
This was very useful, thank you very much. This works perfectly but the same as a normal VLOOKUP this brings the first viable answer. In my case I have three matches and would like to bring the last one.
I could do this in a VLOOKUP by using the MAX function, is there a way I could incorporate that into this formula?
Thank you
Thank you so much! It really helped a lot! 🙂
Thank you for this! It works, but I still have one issue. There can be anywhere from 1-3 rows that match my two criteria. In cases where there are multiple matches, I want to return their sum, how can I do this?
Try SUMIFS(). When dealing with numbers, it will be easier to work with than the INDEX(MATCH()) arrangement.
Great stuff, thanks for the pro-tip!
Can I place my array formula within an IF statement? Such that:
{=INDEX(‘Sheet1’!B1:H96,MATCH(1,(‘Sheet1′!B:B=’Sheet2’!C5)*(‘Sheet1′!E:E=’Sheet2’!BH1),0),7)}
…will execute as the ELSE condition for:
=IF(B27>0,B27-1,
Hi!
Thanks for the tutorial, this worked great!!
I’m having one slight issue though, I quickly skimmed the comments and couldn’t find anything that helped but sorry if this has been asked before.
The problem I have is that I have 3 criteria, one of which isn’t an exact match, so I have been using the greater than symbol, so my formulas read something like this:
=iferror(index(A:D,match(1(A=A2)*(B=A7)*(D>J2),0),3),0)
This works fine on some occasions, except that I have 3 sheets, so criteria one is the date, criteria 2 is the name, and criteria 3 (which is different for all 3 sheets) is a 3 letter code which in the source spreadsheet has spaces after it (it’s a report that’s been generated and I can’t change it). It doesn’t work if there is no figure to pull through for that date, but there is for one of the other 3 digit codes (usually the one alphabetically after) so it will pull that figure through.
What I’m currently doing is this:
=iferror(index(A:D,match(1(A=A2)*(B=A7)*(D>J2)*(D<Sheet1!J2,0),3),0)
Which is fine, but I have about 70 such spreadsheets (each with 3 tabs) and it's a pain to have different formulas depending on the 3 digit code.
I have tried using =iferror(index(A:D,match(1(A=A2)*(B=A7)*(D=J2&"*"),0),3),0), however this pulls nothing through
Any help would be much appreciated 🙂
Thanks in advance!
hy ! I was reading your article regarding “VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)”. It was very useful for 2 criterias. It worked. But it only works when we press Ctrl + Shift + Enter.
If someone is reviewing the sheet, who is not much expert in the excel, and he press F2 and ENTER. The whole formula will be gone in the relevant cell. Please let me know any Technique that the formula will be permanent even after pressing ENTER rather than Ctrl + Shift + Enter.
Thank You
How can the formula be changed to match the data in one sheet to an array in another sheet? I tried putting the ‘SHEET’! in front of the cell ranges, but returns an N/A.
on large datasets (w/ 1000s of rows) i think it is better to make a helper column and then use vlookup. but, i do like this solution
Hello,
I wanted to know on how can I pull data for a certain information like “XYZ” from the data provided below.
Item Set Code Test
ABC, IJK, RST, XYZ 5 2001 Major
ABC 2 251 Major
IJK 6 4001 Remission
RST 6 9002 Depression
XYZ 9 12003 Remission
IJK, XYZ 10 8009 Remission
ABC, RST 11 4007 Depression
Thanks
My question
How to keep the same item in different row if we have more than 1 number of item. For example, if we have item “A” with quantity “5” then I need “A” in 5 different row, each row containing only 1 “A” using VBA UserForm.
how to pick the previous date value if the criteria date value if not available in range.