Comments on: Excel INDEX MATCH with multiple criteria - formula examples

Although Microsoft Excel has special functions for vertical and horizontal lookup, expert users normally replace them with INDEX MATCH, which is superior to VLOOKUP and HLOOKUP in many ways. Among other things, it can look up two or more criteria in columns and rows. Continue reading →

Comments page 3. Total comments: 293

  1. Hello.

    I have 2 sheets. The first sheets has part numbers, Qty produced and Material Name, Material Needed(has formula) The second sheet has the part numbers and qty produced( exported from our system) I wanted to vlookup the Qty Produced on sheet 2 to sheet1 so I could compute my material requirement. Some of the values displays but most displays "N/A". I have already changed the format for both sheets to text or numbers so they would have the same but the N/A still appears. The part numbers which is the lookup value are numeric and alpha numeric. Thank you and I hope you can help me with my problem.

    1. Hi!
      Unfortunately, without seeing your data, it is difficult to give you any advice. N/A error means no value was found. There may be extra spaces or other characters.

  2. Worked perfect. Thanks!

  3. Sorry formula is:

    INDEX($B$3:$B$7,MATCH(1,(--(A11=$A$3:$A$7))*(--("Beer"$C$3:$C$7)),0))

    1. Sorry again, but the post is not showing "does not equal" to Beer.

  4. Is there an INDEX and MATCH formula that can exclude values that match a criterion but does not require the use of control + shift + enter?

    For example, I want a formula to exclude all values that matches to “Beer” but return all other values. I have something in mind like:

    INDEX($B$3:$B$7,MATCH(1,(--(A11=$A$3:$A$7))*(--("Beer"$C$3:$C$7)),0))

    But this formula only works with control + shift + enter. Is there a formula that does not require control + shift + enter?

      1. Thank you. Unfortunately, I don't have a copy of Excel365. I am currently using Excel from Microsoft Home Office 2019. I'm looking for a formula that does not require Ctrl + Shift + Enter since the braces surrounding the formula are easily removed when the array is edited.

          1. Hello, you are not answering the question. I have already explained why an array formula won't work for me. I am looking for a non array formula. But thanks for your time.

            1. The issue you want to solve is unknown to me. But if you need to find values by the criterion, then in your Excel you can do this only with help of array formulas or VLOOKUP function. Other functions are not available to you. I answered your question?

  5. Hi
    I'm using this formula to search the booking number from the table but it won't work for multiple results
    ={INDEX(Table1[Booking],MATCH(1,(ISNUMBER(SEARCH(A1,Table1[Remarks]))*(ISNUMBER(SEARCH(B1,Table1[Remarks])))),0))}
    How can I change these formula to list out all results?

    Thanks!

  6. Hey there, i have problem, my boss ask me to compile big data where i have 2 column and 1 column have multiple criteria, he want me only show result which is not 0.
    example:
    A | B | C |
    -----------------------------------------
    Anna | Sales 1 | 1 |
    -----------------------------------------
    Anna | Cashier | 36 |
    ----------------------------------------
    Anna | Sales 2 | 0 |
    ----------------------------------------
    Sally | Sales 1 | 0 |
    ----------------------------------------
    Sally | Cashier | 75 |
    ----------------------------------------
    Sally | Sales 2 | 2 |
    ----------------------------------------

    just imagine i have big data more than the 2 criteria in coloumn B, and my boss ask me to take only Sally in sales area which not 0 ( cause in column C, only have 0 and other number but have more than 1 sales type) so the result should be

    A | B |
    -----------------------
    Sally | 2 |
    -----------------------
    Anna | 1 |

  7. I want to know how to compare 2 column such as:
    A B
    ABC | GOOD
    ABC | GOOD
    ABC | GOOD = TRUE

    A B
    ABC | GOOD
    ABC | GOOD
    ABC | BAD = FALSE

    Is it possible?

    1. Hi!
      If I understand correctly, you want to determine if all values in the column are the same. Use COUNTIF function.

      =COUNTIF(A:A,A1)=COUNTA(A:A)

      If this does not help, explain the problem in detail.

  8. Hi,

    I have a spreadsheet where I need to get the value (string of text) in column A that is in the cell to the left and 1 down from a non-blank value in column B. So, the set up of the spreadsheet has headers in row 1, then rows A2-A4 list strings and B2-B3 are empty (blank) then B4 has a number, repeat with A5-A7 strings and B5-B6 empty then B7 a number, . . .. What I need is the string from A2, then A5, then A8, . . .. I've tried some formulas with index, match, isblank, and offset, but I'm still too novice to excel to get this figured out quickly.

    Thanks for your help!

    1. Hello!
      To get a list of values by condition, use the FILTER function. To take the value of the cell to the right of the current one, use the OFFSET function.
      I believe the following formula will help you solve your task:

      =FILTER(A2:A20,NOT(ISBLANK(OFFSET(A2:A20,-1,1))))

      1. Thanks, the suggested formula worked perfectly!

  9. Hello,

    I need to come up with the index match formula to get the data from my large data sheet. I have a table in excel with the same questions but different answers and comment for each state, I need to pull all the data into the tabular display. I want to click o the cell and type e.g. Oklahoma and all my data will be pulled into my new sheet. Does anyone have idea how to do this?

    Thank you in advance!

  10. Is it possible to use your methods to utilize OR/AND functions? In my data I have 3 criteria to compare:

    Table1:

    Column A: Name
    Column B: Question 1 (Yes/No)
    Column C: Question 2 (Yes/No)
    Column D: Question 3 (Yes/"Blank")

    The goal is to list all the rows where the answers to EITHER question 1 -OR- 2 is "Yes" -AND- the answer to question 3 is "Yes".
    As a result, any row where question 3 is blank would be excluded, and any rows where BOTH questions 1 and 2 are "No" are also excluded.

  11. Hi,

    How would you write the "Matrix lookup with multiple criteria"- specifically the part "MATCH(H2&H3,B1:E1&B2:E2) using the indirect function?

    I was typing it as ,MATCH($A$4&"*"&$A63&"*",INDIRECT("'"&A$1&"'!$A:$A")&INDIRECT("'"&A$1&"'!$B:$B")
    but it keeps coming up as an error

    thank you

      1. Hi,

        Thank you for your reply, sorry I haven't been clear. So, it's already a two dimensional INDEX MATCH MATCH function as I'm working on a cashflow, where there are two columns of criteria and one row. So at the moment it is working fine like this:

        =IFERROR(INDEX('Sheet1'!$A:$Z,MATCH($A$4&"*"&$A32&"*",'Sheet1'!$A:$A&'Sheet1'!$B:$B,0),MATCH(F$2,'Sheet1'!$A$1:$Z$1,0)),0)

        However, I would like to make it more dynamic by using an indirect function for 'Sheet1'!', Is this possible?

  12. thank you in advance.
    what will be my formula if I want to get the column header starting from given criteria.
    Example:
    01-Jul 02-Jul 03-Jul 04-Jul 05-Jul 06-Jul 07-Jul 08-Jul 09-Jul 10-Jul
    A 1 1 1 1 1 R R R 1 1

    i want to get the end date of my training given that I have 4 days duration (not including restday R).
    start date would be 04-Jul.
    what would be my formula if i want to start counting the 4 days from 04-Jul, result should be 10-Jul.

    thank you

  13. Great alternative to nested IFs. How do I make the formula, from your example, contain "and/or" criteria rather than purely having "and" criteria:

    =INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

    1. Hello!
      Multiplying criteria means an AND condition. Summing criteria means an OR condition. Replace * with +.

  14. Just a quick note to thank you for the how-to! Though I am slowly picking up cool tricks in Excel I doubt I would have ever figured this out on my own. Had to tweak to be able to drag formula down multiple cells but managed to match up data from multi spreadsheets with thousands of rows. Just did =INDEX(Sheet9!C:C, MATCH(1, INDEX((A2=Sheet9!A:A) * (B2=Sheet9!B:B), 0, 1), 0)) which was scary spinning wheel slow and sure there is cleaner ways to do but it worked. Much appreciate!!

  15. How would I write a formula that is capable of checking across multiple sheets to find a specific date and then pull the data from the particular sheet that the date appears on?

    I have searched high and low through numerous sites and forums and have not been able to find anything that fits the bill.

    I am using Excel 2010. The data being pulled is just numbers to one decimal point.

    What I have.
    The first sheet (Last 31 Days) is where I am pulling the data to. I have a formula that updates the dates shown each day so that the individual cells in column B show the last 31 days.
    The second sheet (2022) contains data for 2022 separated into each month (actual and dummy data).
    The third sheet (2023) contains data for 2023 separated into each month (dummy data).
    All 3 sheets are exactly the same format and layout.

    What I would like the formula to do is to check the "2022" sheet for the required date and pull the data from that sheet if found, but if it does not find the required date, then moves to the "2023" sheet to find the required date and pull the data from that sheet if found.

    Formulas I have that work individually.
    =INDEX('2022'!$C$3:$C$389, MATCH(B3, '2022'!$B$3:$B$389, 0))
    =INDEX('2023'!$C$3:$C$389, MATCH(B3, '2023'!$B$3:$B$389, 0))

    B3 - the cell that contains the date on the "Last 31 Days" sheet.
    '2022'!$B$3:$B$389 - the cell range that the date is being searched on the "2022" sheet.
    '2022'!$C$3:$C$389 - the cell range that the data is being pulled from on the "2022" sheet.
    '2023'!$B$3:$B$389 - the cell range that the date is being searched on the "2023" sheet.
    '2023'!$C$3:$C$389 - the cell range that the data is being pulled from on the "2023" sheet.

    Thank you in advance.

      1. I need to count the number of sales orders where the pallet quantity is greater than 21. The same sales order number can appear on multiple rows, and the pallet count is tied to each line. In my formula it isn't summing up pallet quantity if a sales order appears multiple times. Assuming I need some sort of combo of count and sum?

        Criteria: Count # of sales orders where (Customer = Mike) (Sum of Pallet quantity >=22) (Package Type = Case)

        Desired result: 2

        Current Formula =COUNT(FILTER(SalesOrderNumber!A:A,( (Package Type!C:C="Case")*(Customer!E:E="Mike")*(Pallet Quantity!D:D>=22) )))

        SalesOrderNumber ItemNumber Package Type Pallet Quantity Customer
        16590 14590056 Drum 23 Amy
        17950 14590033 Case 23 Mike
        17806 33202332 Case 10 Mike
        18900 33202332 Case 11 Mike
        18900 33202332 Case 11 Mike

        1. Hello!
          According to the data and conditions you specified, the result cannot be equal to 2. Here is the SUMPRODUCT formula for calculating according to your criteria

          =SUMPRODUCT(--(E2:E6="Mike"),--(D2:D6>=22),--(C2:C6="Case"))

          Result = 1.

  16. Thank you for this great article! Is there a way to pull data from a different column if the formula wasn't able to find anything on the original column? ie. if 'Device List'!E2:E190=0, pull data from 'Device List'!F2:F190 instead?

    =INDEX('Device List'!E2:E190,MATCH('User Details'!A2&'User Details'!D2,'Device List'!H2:H190&'Device List'!K2:K190,0))

    Thank you!

  17. Thank you, Alexander! This was exactly what I needed to be able to select data from one of multiple columns based on two values in the column headers.

    Happy 2022 to you Ablebits team!

  18. I have the following formula:

    =IFERROR(INDEX(DATA!$A$2:$A$100,SMALL(IF(DATA!$D$2:$D$100"",ROW(DATA!$A$2:$A$100)-ROW(DATA!$A$2)+1,""),ROW(1:1))),"")

    This finds the first entry from column A that does not have a blank entry in column D and returns the value before moving to the next row.

    What I would like to do is add a second check into this based on 4 letters (such as "ABCD" or "TFSF") and for some reason I can't get the INDEX MATCH to work correctly.

    The DATA sheet is an separate indexed sheet that parses data from a third sheet with merged cells.

    Basically, find first number with blank in in column D in the DATA sheet that also has ABCD in Column C then go to the next row.

    thanks for any help on this!

    1. Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Unfortunately, without seeing your data it is difficult to give you any advice.

  19. Is it possible to use match for partial matches in a string in this kind of formula? for example i would want to match for a cell that matches an "ABC" into an array that would have a cell matching with ABC-XXXX. So the match is only partial. I tried using &"*" and they are giving me #ref

    thank you so much.

    1. Hello!
      To find a partial match of the text, use the SEARCH function.
      Here's an example of a formula:

      =INDEX(A1:A10,MATCH(TRUE,ISNUMBER(SEARCH("ABC",A1:A10)),0))

      Hope this is what you need.

  20. How does the formula work with multiple conditions on both X and Y matches?

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  21. Hi Alex,
    I am trying to apply the formula in the example above but with additional criteria of selecting the maximum value. For instance, If C5 is replaced with Apples, what formula would give G4 as $130

  22. Hello

    I am having trouble applying this. I am trying to match a row of data from one sheet to match the exact set of variables from another sheet and pick up the 8th entry associated with it, the order amount.

    For example I want to find the entry that matches exactly these details from sheet1 in sheet 2 and where they match exactly pick up the order amount indicated in sheet 2. These are columns and rows. So if the entry I am looking at is "Sheet1!A3:G3" and looking for this exact entry in "Sheet2!"A1:G1000". Lets say they match on row 15 of sheet 2 then I want to pick up the entry in "Sheet2!H15

    Sheet1 1 entry
    product, weight, type, grower, supplier, available, price, order
    zucchini, 8kg, box, organic, vikram, yes, 30, (information needed)

    Sheet 2
    product, weight, type, grower, supplier, available, price, order
    zucchini, 8kg, box, organic, vikram, yes, 30, 3

    So I want to pick up the order amount of 3 from Sheet2 using this approach.

    Ive tried but am not getting it right. Could you please help me?

    Thanks very much,

    Warm regards

    Nicholas

    1. Hello!
      You can use something like this:

      =INDEX('Sheet1 (2)'!H2:H10,MATCH(1,(Sheet1!A2='Sheet1 (2)'!A2:A10)*(Sheet1!B2='Sheet1 (2)'!B2:B10),0))

      I only used 2 conditions. You can find examples and detailed instructions in this article above.
      I hope it’ll be helpful.

  23. Hi Alexander,

    This article has been super helpful for a novice like myself! I have a question, I'm trying to create a formula for determining the shortest distance between two zip codes and I have the table set up in a matrix. How would I go about pulling in the lowest number with the table looking like this below?
    For 21607, 21640, 21864 - I would need a formula to determine which number in each row is the lowest. Thank you in advance.

    12110 87110 18034 50010
    21607 272 374 100 950
    21640 274 522 105 955
    21864 336 487 170 999

  24. The Matrix lookup with multiple criteria - formula example really helped me a lot. However, I also want to get the sum of multiple cells based from the criteria. How can i incorporate to get the sum of values? or should i use sumifs instead? Hope to hear from you the soonest. Thanks!

      1. Here are my criteria:
        1. Interval: from Interval 1 to 288
        2. Plant: Plant 1, 2, 3 (this is the criteria I want to sum the values, there are duplicate plants in a day)
        Date:

        Below is the data were I get the values applying Matrix lookup with multiple criteria:

        Interval 7/26/2020 7/26/2020 7/27/2020 7/27/2020 7/28/2020 7/28/2020 7/28/2020
        (every 5mins.) Plant 2 Plant 1 Plant 1 Plant 1 Plant 2 Plant 3 Plant 1
        1 0 0.0517 0 0.0532 0 0 0.0530
        2 0 0.0522 0 0.0521 0 0 0.0532
        3 0 0.0514 0 0.0523 0 0 0.0529
        4 0 0.0519 0 0.0522 0 0 0.0531
        5 0 0.0515 0 0.0518 0 0 0.0532
        .

        .
        288

        I have used the matrix lookup with multiple criteria. However, INDEX is a lookup function and will only ever return a value from a single cell. Though the values are 0, I want to get the sum of the plants for every date and interval..

        Here is my formula:
        =IFERROR(INDEX('Plant A'!$C$6:$BV$293,MATCH('Summary_Plant A'!C6,'Plant A'!$B$6:$B$293,0),MATCH('Summary_Plant A'!B6&'Summary_Plant A'!$D$5,'Plant A'!$C$4:$BV$4&'Plant A'!$C$5:$BV$5,0)),0)

        I did try to add a sum function but the result if not zero (no value), wrong result, #value or no change in the result. How can I incorporate sum/sumif/sumifs function in the formula? I am also wondering if I should use the sumifs function instead of the index-match? need your assistance to create the correct formula. Hope the sample above helps to visualize the task. Thank you.

        1. Hello!
          If I understand your task correctly, the following formula should work for you:

          =SUMPRODUCT(B3:H100*(B1:H1=K1)*(B2:H2=L1))

          K1 - date
          L1 - plant

          1. Hello,

            The formula worked well. Thank you for your prompt assistance!

  25. I am trying to figure out how to use this to match 2 criteria, with index returning the max value of the second criteria. The data is formatted in 3 columns:

    Column A - Employee name
    Column B - Manager name
    Column C - Date assigned to that manager

    An employee may have multiple records in the table if they were under different managers at different times.

    I want to determine the name of the manager the employee was assigned to on a specific date. So, I want to match the employee's name with Column A, then find the max date that is after my search date - and return the manager's name.

    For example, if my data looked like this:

    Employee A -- Manager Z -- 1/1/2008
    Employee A -- Manager Y -- 8/1/2015
    Employee A -- Manager X -- 11/28/2018

    I want to be able to search for Employee A on an arbitrary date, let's say 12/25/2017 - and get the answer back of Manager Y

    2. Although ideally the data would always be arranged in chronological order where the last matching result would be the correct one, I'd like the formula not to depend on that if possible since I'm not the only one entering data in the table!

      1. I have tried with the non-array formula as well, but no progress. Still no clue how to select the record with the max assigned date, and the formula is still giving me #N/A

        =INDEX(EmpTable[Manager],MATCH(1,INDEX(([@Employee]=EmpTable[Employee])*([@JobDate]>=EmpTable[AssignedDate]),0,1),0))

      2. I am entering it as an array function using Shift + Ctrl + Enter

      3. For reference, this is what I have so far, but it's not working:

        EmpTable has 3 columns: Employee -- Manager -- Assigned Date
        My jobs table, where I'm trying to put this formula, has columns for Employee and JobDate

        =INDEX(EmpTable[Manager],MATCH(1,([@Employee]=EmpTable[Employee])*([@JobDate]>=EmpTable[AssignedDate]),0))

        This formula gives me a result of #N/A, even though I'm entering valid search data

  26. Hi, is it possible to drag one of the 1st 2 formulas into other rows of the sheet? I tried it but it seems like it didn't work.

    1. I have a column where each cell needs to have the formula applied. It does work on a single cell but not when I try to drag the formula to the other rows of the same column. Thanks again.

        1. Sorry for the confusion but thanks I found what was wrong. The formula does work when dragging it down the column. Thanks again.

  27. great example, thank you for information,
    however can we place more than 3 criterias for the index-match formula? index(match(criteria1),(criteria2),(criteria3),(criteria4),etc

  28. Hello,

    I am trying to figure out how to utilize 3 criteria to pull a count from a table array. The fields in the table needing to be used Keyed By, Time Elapsed, and Date. Each of these fields can have multiples of the same name, time elapsed, and date. My goal is to count the amount of times the keyed by rep has exceeded this time elapsed goal on a specific day which is just going to be entered in a Date cell. I believe I need to use countifs and index match but struggling to figure out how to enter the function.

    Thanks,
    James

  29. Hi, I would like to use this index and match formula with a minimum formula as well. So i have to match on multiple criterea. I have tried to explain below, I need the return to match the name and the closest no.

    name no. name no. return
    x 5 x 4 5
    y 5 x 8 10
    x 10

    1. That table got messed up

      x 5 x 4 5
      y 10 x 8 10
      x 10

  30. Need help to convert multiple row into multiple column by using index formula.

    My data is

    1 a
    1 b
    1 c
    2 a
    2 b
    2 c
    3 a
    3 b
    3 c
    3 d
    3 e
    4 a
    4 b
    4 c
    5 a
    5 b

    then the output will be.

    1 a b c d
    2 a b c
    3 a b c d e
    4 a b c
    5 a b

    1. Hi Viki,

      Your task can also be accomplished with our Ultimate Suite:

      - First, you run Merge Duplicates Wizard to merge duplicates into one cell by the 1st key column. For the delimiter, use a character that is not present anywhere in your data.

      - Then, you use the Split Text tool to split the merged values into multiple columns.

  31. Thanks, that was driving me nuts!

  32. I'm looking to return a 3rd value based on if two cells each meet certain criteria. For example, (in a real estate context), if anything in column A = "1 BR/1 BA" and anything in column B = 30%, return the value in column C associated with 1 BR/1 BA and 30%. The closest I've gotten so far is with the following formula:

    =INDEX(B5:B19, MATCH("1 BR/1 BA, C5:C19,0)*MATCH(30%, D5:D19,0))

    This has worked in some instances, but not all, and I'm not sure why. Thank you for any assistance you can provide!

    1. Hello!
      Read the paragraph above carefully: Non-array INDEX MATCH formula with multiple criteria.
      It contains answers to your question.

  33. It is very nice to have this article.

    If I have two excel/csv files, can I make a new file to pull the data to it? (the criteria is in A file, the data is in B file)

    Thanks.

  34. Stupid web codes...

    =@INDEX(H25:N35,IF(B12 < 2.1,1,IF(AND(B12 > =2.1,B12 < 3.1),2,IF(AND(B12 > =3.1,B12 < =4),3,IF(AND(B12 > =4.1,B12 < 5.1),4,IF(AND(B12 > =5.1,B12 < 6.1),5,IF(AND(B12 > =6.1,B12 < 7.1),6,IF(AND(B12 > =7.1,B12 < 8.1),7,IF(AND(B12 > =8.1,B12 < 9.1),8,IF(AND(B12 > =9.1,B12 < 10.1),9,IF(AND(B12 > =10.1,B12 < 11.1),10,IF(AND(B12 > =11.1,B12 < 12.1),11,0))))))))))),IF(B7 < 2501,1,IF(AND(B7 > =2501,B7 < 3001),2,IF(AND(B7 > =3001,B7 < 3501),3,IF(AND(B7 > =3501,B7 < 4001),4,IF(AND(B7 > =4001,B7 < 4501),5,IF(AND(B7 > =4501,B7 < 5001),6,IF(AND(B7 > =5001,B7 < 5501),7))))))))

  35. I am trying to get a value returned based on a table of information with ranges. Below is the table I am working with. It is the bags requirements for a house foundation based on the square footage of the house slab, and the average height of the slab. I want to be able to return a value based on the two separate inputs and return the correct row and column.

    Example: the house slab is 3073 sf (this is B7 in the spreadsheet) and the average height is 4.08 ft (this is B12 in the spreadsheet). That should come out to be 15 (third column, fourth row).

    Height 2000-2500 2500-3000 3000-3500 3500-4000 4000-4500 4500-5000 5000-5500
    1.0-2.0 8 9 12 14 15 16 17
    2.0-3.0 9 11 13 15 16 17 19
    3.0-4.0 10 13 14 15 17 19 20
    4.0-5.0 11 14 15 16 19 20 22
    5.0-6.0 12 15 16 17 20 22 22
    6.0-7.0 13 16 17 18 22 22 23
    7.0-8.0 15 17 18 19 22 23 24
    8.0-9.0 16 18 19 19 23 24 26
    9.0-10.0 17 19 20 20 24 26 26
    10.0-11.0 18 20 21 22 26 26 27
    11.0-12.0 19 21 22 22 26 27 28

    Note: The table in my worksheet spans G24:N35

    The problem I am getting into is that I don't have specific values I am checking for along the rows and columns, but ranges in both. The current formula I have is below, but I am getting a #VALUE! error due to the 4.08 average height getting missed in my formula. Is there a better way to format this formula to manage all the ranges of the two values I am needing to check in the table or am I really stuck with all the nested IF's?

    =@INDEX(H25:N35,IF(B12=2.1,B12=3.1,B12=4.1,B12=5.1,B12=6.1,B12=7.1,B12=8.1,B12=9.1,B12=10.1,B12=11.1,B12<12.1),11,0))))))))))),IF(B7=2501,B7=3001,B7=3501,B7=4001,B7=4501,B7=5001,B7<5501),7))))))))

    1. Hello!
      Write the first line as 2000 2500 3000 etc.
      Write the first column as 1 2 3 4 5, etc. If 4.08 is written in I1, in 3072 it is written in I2, then you can use the formula

      =VLOOKUP(I1, A2:H12, MATCH(I2, A1:H1, 1), 1)

      Please check out this article to learn how to Vlookup based on row and column values.
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Much cleaner formula. Works perfectly, thanks!

    2. Hmm, the comment formatting broke my formula...that's not what it's suppose to read as. Maybe this will work.

      =@INDEX(H25:N35, IF(B12=2.1 , B12=3.1 , B12=4.1 , B12=5.1 , B12=6.1 , B12=7.1 , B12=8.1 , B12=9.1 , B12=10.1 , B12=11.1 , B12<12.1) , 11 , 0))))))))))) , IF(B7=2501 , B7=3001 , B7=3501 , B7=4001 , B7=4501 , B7=5001 , B7<5501) , 7))))))))

  36. Excellent explanation and detail - thank you for posting!

  37. Hi, I have 2 columns that has Performance rating of 2 years. Say,
    Column A = 2 Column B =1 column C: an amount 3000 This will be in different combinations like, 1,2, 1,3, 2,1 etc for different employees

    I need to calculate :
    If column a=1, column b=2, then it should fetch Column C *2+200
    If column a=2, column c=3, then it should fetch column C* 0.5+ 300

    Similarly, different combinations of ratings for 2 years should fetch a value where different formulas are applied. How do I do that?

  38. Hi, thank you for explaining the Index/match function.

    I get the formula to work outside of 'tables', but not between tables.

    Formula is written like: {=Index(Cardata[emissions];Match(1;(Form[fueltype]=Cardata[fueltype])*(Form[cartype]=Cardata[cartype]);0))}

    It should return a corresponding emission number.

    ***Is there another way to make this formula work between tables?***

    Much appreciated!

    Rolf

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice.
      Is your formula not working? Please describe the problem in more detail.

  39. This formula worked for me.

    SUM(COUNTIFS($A$2:$A$10,H13,$B$2:$B$10,I13,$C$2:$C$10,J13,INDEX($D$2:$J$10,,MATCH($J$14,$D$1:$J$1,0)),{"=0","**"}))

  40. can reverse back the formula?
    which mean I put some price, I know the product from which region.

  41. Thank you for prompt reply.

    Data Sheet
    HDR-1 HDR-2 HDR-3 HDR-4 HDR-5 HDR-6
    ST BG DC 60 NA 35
    GT CG DB 20 40 40
    ST BG DC NA 60 90
    ST CG DB 30 20 NA
    ET BG DC 30 20 20
    ST BG DC 50 10 10

    Below Cell Values need to be Match in columns and header of Data sheet

    ST BG DC HDR-4

    HDR-4 Count Result = 2

    1. Hi,
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =SUM(IFERROR((A2:A10=$H$8)*(B2:B10=$I$8)*(C2:C10=$J$8) * (ISNUMBER(INDIRECT(ADDRESS(2,MATCH($J$14,A1:J1,0))&":"&ADDRESS(2,MATCH($J$14,A1:J1,0))&"00"))),0))

      $J$14 -- HDR-4
      H8:J8 -- ST BG DC

      I hope it’ll be helpful.

      1. Thanks Alexander for the answer, unfortunately, this formula doesn't work for me. I will try to explain this issue in more detail. Suppose I have data from A2 to J10 and A1 to J1 is the data header (HRD-1, HRD-2....HRD-10 ).

        Now I will describe the criteria.
        Assume that 1st Criteria is in cell H13 to J13, as stated in formula (A2:A10=$H$13)*(B2:B10=$I$13)*(C2:C10=$J$13). And 2nd criteria is in J14, as stated in the formula MATCH($J$14,A1:J1,0).

        In K14 I want to count the total number of numeric data of each column (HD-4 column {i.e D2:D10
        }...... to........ HD-10 column {i.e J2:J10}) by keep changing cell criteria in H13 to J13 as well as the header criteria in cell J14.
        Hope you understand my description.

  42. HI,
    Can someone help me to add INDEX(Data!A1:P1,MATCH(J14,Data!A1:J1,0)) formula to COUNTIFS(Data!A2:A4000,H8,Data!B2:B4000,I8,Data!C2:C4000,J8,Data!F2:F4000,">0"). I am trying to count numerical cells where Header and column Criteria is being matched.

    Thank You

    1. Hello!
      The COUNTIFS function uses only range references as criteria_range. Therefore, you cannot use the INDEX function for this. So that I can give you advice on how to write a different formula, please describe your problem in more detail.

  43. Hello,

    Is there any reason why the function below will not work for "INDEX MATCH with several criteria - formula example"? I still get $115 when I use the formula below (without pressing Ctrl + Shift + Enter).

    =INDEX(D2:D13,MATCH(G1&G2&G3,A2:A13&B2:B13&C2:C13,0))

    Thank you!

    1. Hi,
      Unfortunately, without seeing your data it is difficult to give you any advice.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

      1. Hi Alexander,

        Appreciate the reply! I was referring to the first example shown on this page.

        The first formula is what Svetlana shows to get to the answer, but I was wondering why an array formula needs to be used if the second formula also gets me to the correct answer.

        ={INDEX(D2:D13, MATCH(1, (G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13), 0))}

        =INDEX(D2:D13,MATCH(G1&G2&G3,A2:A13&B2:B13&C2:C13,0))

        Thanks,
        Scott

        1. Hello!
          Two solutions are always better than one. You can use any formula. However, Microsoft warns that the string concatenation operation is computationally intensive. Therefore your second formula with a lot of data will be slower than the others.

          1. Appreciate the help!

  44. Hi, I want to search a cell range for a string and place the matching cells into a new column.

    For example,
    In Cells A1:A5 below... Column P

    AAL Jan 08 2021 18.5 Call AAL Jan 08 2021 18.5 Call
    CBD DENVER INC (CBDD) DAL Dec 31 2020 42.0 Call
    DAL Dec 31 2020 42.0 Call
    FCEL Dec 18 2020 8.0 Put
    SANUWAVE HEALTH INC (SNWV)

    I want to find all the "Call" strings and place them sequentially in column P (shown above).
    How can I use the index and if macros to do this? Or how would you do this?

    Thanks!

  45. Your example file doesn't work lol - there is #Value! in cell with result if i push ENTER to recalculate your formula.

    1. Hello George,

      If it's an array formula, then you should press Ctrl + Shift + Enter to recalculate it. I've just checked all the examples in our sample workbook and all 3 formulas recalculated just fine.

      If the error persists on your side, please let me know which example does not work and what Excel version you are using.

  46. I'm having issues with Index + Match working correctly. Here's what I am trying to do:
    > find a Number in a row based on two separate search criteria, each in its own row:

    Criteria 1 is to find a specific Date in row range B1:G1
    Criteria 2 is to find a specific Word in row range B2:G2
    The Number I need to find is in the Column of row 3 where Criteria 1& 2 are matched.

    Thanks in advance.

  47. Hello Alexander,
    Please help, i am stuck at a report which can be understood from below table eg:
    i need to index marks in similar table with only those students names who have marks less than 40, along with marks in similar column but only less than 40. I am not sure which function to apply but trying
    index only without success so far.

    Student Math History English Science
    A 35 70 85 20
    B 55 64 30 81
    C 47 49 40 79
    D 62 52 94 27
    E 15 35 50 32
    F 38 75 29 19

    Thanx in advance

    1. Hello!
      If I got you right, please check out this article to learn how to VLOOKUP multiple values in Excel with one or more criteria.
      To help you write your formula, describe in detail the result you would like to get from your data.

      1. Thanks for your help Alexander, the result i would want is some thing like below table, since
        only those marks are shown which are less than 40 (i have put - to represent blanks), and only those student names should appear
        whose marks are less than 40, like in below table name of student C is not there. ( I think in vlookup we have to put all the names)

        Student Math History English Science
        A 35 - - 20
        B - - 30 -
        D - - - 27
        E 15 35 - 32
        F 38 - 29 19

        Thanks in advance and apologies to trouble you again.

  48. Hello Alexander,

    This formula is for index match for multiple criteria has really helped me a lot. Thank you! My question is, if all the criteria has been fulfilled but I want the value below that cell to be reflected, how would the formula look like?

    This is a sample formula that I use, when there's just one criteria:
    =INDEX(Sheet39!B:B,MATCH(A3,Sheet39!B:B,0)+2,1)

    And here's an example of the formula with multiple criteria that I would like to reference a cell below:
    =IFERROR(INDEX(Sheet39!B:B,MATCH(1,(Sheet39!D:D=B$3)*(Sheet39!A:A=$A$1),0)),"")

    Thank you in advance!

      1. Hello Alexander,

        Apologies for not being clearer, please see sample below:

        NAME AGE PLACE
        ADAM 10 LA
        BRODY 11 LA
        CHARLIE 12 LA
        ADAM 8 CA
        LUKE 9 CA
        CHARLES. 10 CA

        if I want to see the result for who is the next to ADAM in CA for example (as there are many Adams in different places), but it's important to see the one who is listed next to him (cell below), I am trying to use =INDEX(A:A,MATCH(A4,A:A,0)+1,1) where i typed ADAM on A4. The result here is BRODY.

        So if I want "LUKE" to show, I will then have to create multiple criterias for Adam, to include, say the AGE or PLACE. And unfortunately I am unable to do it with this formula for multiple criterias: =IFERROR(INDEX(A:A,MATCH(1,(A:A=A4)*(C:C=CA),0)),""). I can't seem to place the +1 or +2 cell below to show the result of LUKE or CHARLES.

        Thank you in advance for your assistance!

        1. Hello!
          If I got you right, the formula below will help you with your task:

          =IFERROR(INDEX(A:A,MATCH(1,(A:A=A4) * (C:C="CA"),0)+1),"")

          I hope I answered your question. If something is still unclear, please feel free to ask.

          1. Thank you Alexander for the assistance. Really appreciate it. Have a good day and keep safe!

  49. Hello,

    I have a list of products and it has multiple date. I required all the date against the product one by one.
    when i used the below formula i got the smallest date (1/31/2019) in the list against one of the product. and still there are more 11 dates i should get. but when i copied this formula in the next column its showing an error.
    =INDEX('Evaluation data'!$W$2:$W$12277, SMALL(IF($A2='Evaluation data'!$M$2:$M$12277, ROW('Evaluation data'!$M$2:$M$12277)-ROW('Evaluation data'!$M$2)+1), COLUMN(A:A)))

    Colum M Colum W
    TYRE 1 1/31/2019
    TYRE 1 1/31/2019
    TYRE 1 4/10/2019
    TYRE 1 4/10/2019
    TYRE 1 5/3/2019
    TYRE 1 5/3/2019
    TYRE 1 5/3/2019
    TYRE 1 5/3/2019
    TYRE 1 6/11/2019
    TYRE 1 6/11/2019
    TYRE 1 7/10/2019
    TYRE 1 8/15/2019
    TYRE 1 9/13/2019
    TYRE 1 5/25/2020

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets.
      Please check out this article to learn how to vlookup multiple matches in Excel with one or more criteria.
      I hope my advice will help you solve your task.

  50. hi, thank you for the great explanations!!
    hopefully you could solve my problem:
    i have a table of different plants varieties (column a) and the flowering date (column b) for each variety, i have another table with the height of the plant varieties measured daily (the first column is the list of the varieties and each date of measuring the height is a separate column). i would like to find the height of each variety at its flowering date. i.e find the match between the flowering date and return the height measured at this date
    thanks

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      If you cannot solve the problem yourself, please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com. Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)