How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

Multiple VLOOKUP LeadVLOOKUP is a great tool for pulling data from tables, but it has a handicap: it can only work with one criteria for matching information. If there are multiple rows in your sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck. Fortunately, Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria. Here’s a quick tutorial to help you learn how…

Example Data

Let’s say, for example, that we want to be able to search through a list of fuel economy data for cars to find the mileage…

Car Data

Normally, we would want to be able to enter the model of a car and get it’s fuel economy as a result. Unfortunately, Many cars, like the Acura ILX, have multiple engine configurations with different mileage ratings. Fortunately, in this case, the car’s displacement can serve to separate them.

Displacement Highlight

This means, however, that we will need to look up the car by both its Model and its Displacement at the same time to find the appropriate Combined Fuel Economy in column H.

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Jump start your analysis with a one-on-one consultation or join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

121 thoughts on “How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

  1. 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.

  2. 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…

  3. 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.)

  4. 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!

    1. 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

      1. 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 …

  5. 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?

    1. 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.

    1. 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?

  6. 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

    1. 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.

  7. 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.

    1. 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

  8. 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

    1. 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

      1. 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.

  9. 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

  10. 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!

  11. 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!

  12. 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])

  13. 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.

  14. 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!

  15. 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.

  16. 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?

    1. 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

  17. 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!

    1. 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

  18. 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

  19. 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.

    1. 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.

  20. 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

  21. 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.

  22. 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?

  23. 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)

      1. 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?

    1. 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)}

  24. 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,

    1. 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…

  25. 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.

  26. 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

    1. 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

  27. 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.

  28. 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!

  29. 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!

  30. 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)

  31. 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?

  32. 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

  33. 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.

  34. 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?

  35. 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!

    1. 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.

      1. 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!

  36. 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!

  37. 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!

  38. 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.

    1. 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.

  39. 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

  40. 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

  41. 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?

  42. 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

  43. 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.

  44. 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.

  45. 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…..

  46. 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)

  47. 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.

  48. 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

  49. 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

  50. HI !

    Sahin look its:
    Origin Date Sr.NO SR NO ORIGNIGATION COUNTRY CURRENCY EFFECTIVE DATE END DATE
    A 9-Feb-15 2 1 A USD 9-Mar-15 9-Mar-15
    2 A USD 9-Feb-15 9-Feb-15
    3 A USD 9-May-15 9-May-15
    4 A USD 9-Feb-15 9-Feb-15
    5 A USD 9-Feb-15 9-Feb-15
    6 A USD 9-Feb-15 9-Feb-15
    7 A USD 9-Feb-15 9-Feb-15
    8 A USD 9-Jul-15 9-Jul-15
    9 B INR 8-Feb-15 8-Feb-15
    10 B INR 8-Feb-15 8-Feb-15
    11 B INR 8-Feb-15 8-Feb-15
    12 B INR 8-Feb-15 8-Feb-15
    13 B INR 8-Feb-15 8-Feb-15
    14 C AUD 7-Oct-15 7-Oct-15
    15 C AUD 7-Feb-15 7-Feb-15
    16 C AUD 7-Feb-15 7-Feb-15
    17 C AUD 7-Feb-15 7-Feb-15
    18 C AUD 7-Feb-15 7-Feb-15
    19 D EUR 6-Dec-15 6-Dec-15
    20 D EUR 6-Feb-15 6-Feb-15
    21 D EUR 6-Feb-15 6-Feb-15
    Under below :
    =INDEX($E$23:$I$43,MATCH(1,($F$23:$F$43=$A$23)*($H$23:$H$43=$B$23),0),1)

  51. 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?

  52. 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?

  53. sir i have one dout plz solve problem
    sir we have one problem plz solve this

    ,=INDEX(performance!$E$2:$L$301,MATCH(today!$E2&today!$F2&today!$G2&today!$H2&today!$I2&today!$J2&today!$K2,performance!$E$2:$E$301&performance!$F$2:$F$301&performance!$G$2:$G$301&performance!$H$2:$H$301&performance!$I$2:$I$301&performance!$J$2:$J$301&performance!$K$2:$K$301,0),8)

    this formula was given exject but we want +/- 3% range between plz solve my problem

    thank u Advance

  54. 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

  55. 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))

  56. 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?

  57. 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.

  58. 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))

  59. Header #1 Header #2 Header #3 Header #4 Header #4
    7 5 3 2 2
    Because I have multiple #2’s… the same header comes up. Need the header from the next #2 on my list.

  60. 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.

  61. I tried this formula so many times with unsuccessful result.
    After I follow your tactic, it’s work!!!

    Billion Thanks

  62. whoaaa!!it works!!
    many thanks for this great tutorial.finally understand how to use the index n match function.Keep up the good work!!

  63. 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?

  64. 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.

  65. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>