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 2. Total comments: 293

  1. Hi,
    I have this data special number and total claim(money).
    How I want to pull data total claim (money) but ignore zero because some of the special no has duplicate?

    SPECIAL no Total claim
    231362 48.36
    231361 0
    231361 4200.83
    231363 852
    231364 54.4
    231281 21.92
    231327 21.92
    231328 21.92

    1. Hi! I'm not quite clear what you want to count. If you are counting the sum, then zero is irrelevant. Explain in detail what result you want. You can find the sum for each special number using the SUMIF function.

      1. Thank you very much! That solved my problem! It's really simple solution.

  2. How can I get "column1" and "column2" from the first three column? I need to get latest "etd date" and itsitem names of "modal". Also, if etd date has the value of "11/11/2000", I need to know in a same cell like column 1 an 2.

    Modal Item name etd date column1 column2
    1 x 11/11/2000 1 (y 20/01/2021 + x no etd)
    1 y 20/01/2024 2 (x 10/12/2023)
    1 z 10/01/2024 3 (a,b,c 09/01/2024 + e no etd)
    2 x 10/12/2023 4 (b 20/01/2024)
    2 b 09/12/2023
    3 a 09/12/2023
    3 b 09/01/2024
    3 c 09/01/2024
    3 y 09/01/2024
    3 e 11/11/2000
    4 a 10/01/2024
    4 b 20/01/2024

    1. Hi! Unfortunately, your question is not very clear. Explain it in more detail. Give an example of the result you want to get.

  3. IF any duplicate row will given in this example, and i want to add all same row, then what formula will required?

  4. This page helped to be able to pull one value from a table, but how could I modify to concatenate and pull all values that meet the criteria into one cell?

    *doing a two criteria index match based on a name and "yes" in two different columns. Need it to return all values with this name and "yes", not just the first one*

  5. Column A Column B
    5715363263461 271F0345028709
    5715363263461 271F0345028890

    Need a formula where in if i put column a value in column c then i get 2 different values in column d

    1. Sorry, I do not fully understand the task.
      As it's currently written, it's hard to tell exactly what you're asking.

  6. Can I combined partial text match and if greater than zero match and return with text?

    Example:
    Column A:
    Row1: Apple
    Row2: Banana
    Row3: Grapes

    Column B:
    Row1: $100
    Row2: $0
    Row3: $1

    Formula: match if "apple", >1, return "valid"

    Answer: Valid

    Is this possible?

    Thank you

    1. If I understand the question correctly, you can use the IF function with multiple AND conditions. For example:

      =IF(AND(A1="apple",B1>0),"valid","")

      If you want to detect a partial text match, try this formula:

      =IF(AND(ISNUMBER(SEARCH("apple",A1)),B1>0),"valid","")

      For more information, please read: How to find substring in Excel
      You can also use the MATCH function to determine that at least one row in the range matches the conditions.

      =IF(ISNUMBER(MATCH(1,(ISNUMBER(SEARCH("apple",A1:A10)))*(B1:B10>0),0)),"valid","")

  7. I having issues figure this out. Below is a sample of data. Here the logic I want to apply

    1) For the first row match to a row that date equals month +1 and that has the same division number. Example 2022 07 match 2202 08 and division are both 1001.
    2) subtract count 172 - 64 = 108

    Any suggest for how to do this?

    Division Date Year Month count
    1001 2022 07 2022 July 172
    1002 2022 07 2022 July 3
    1003 2022 07 2022 July 1
    1005 2022 07 2022 July 7
    1006 2022 07 2022 July 27
    1007 2022 07 2022 July 33
    1009 2022 07 2022 July 1
    1001 2022 08 2022 August 64
    1002 2022 08 2022 September 22
    1003 2022 08 2022 October 11
    1005 2022 08 2022 November 40
    1006 2022 08 2022 December 1214
    1007 2022 08 2022 January 76
    1009 2022 08 2022 February 9

    1. Hi! I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Try this formula:

      =F1-INDEX(F1:F14, MATCH(1,(A1=$A$1:$A$14)*(B1=$B$1:$B$14)*((C1+1)=$C$1:$C$14),0))

  8. Hi All,

    I am trying to do a index match formula that keeps coming up with the following error: MATCH range must be a single row or a single column.

    My formula is trying to return a specific cell based on it meeting 3 key criteria's:

    1) Year
    2) Type of investment
    3) Template code

    My formula is as follows:

    =INDEX('Detail'!$1:$91,M
    MATCH(0,(P$5='Detail'!$4:$4)*($C6='Detail'!$B:$B)*($P$4='Detail'!$3:$3),0))

    For some further context:

    Detail is the tab it is looking up from.

    1:91 - data range

    P5 - Type of investment

    C6 - Template code

    P4 - Year

    Any help would be much appreciated.

    1. Hi! If you want to find the first match of criteria using MATCH function, use 1 as lookup_value.

      =INDEX('Detail'!$1:$91, MATCH(1,(P$5='Detail'!$4:$4)*($C6='Detail'!$B:$B)*($P$4='Detail'!$3:$3),0))

      I can't check a formula that contains unique references to your data, which I don't have.

  9. Hello!

    I am using the following: sum(INDEX('[dateland - jackson fuel macro data.xlsm]Jan'!$F:$F,MATCH(1,($X$4='[dateland - jackson fuel macro data.xlsm]Jan'!$E:$E)*($A12='[dateland - jackson fuel macro data.xlsm]Jan'!$C:$C)*($C12='[dateland - jackson fuel macro data.xlsm]Jan'!$D:$D),0)))

    My problem is, it is only summing the first number that matches the criteria, and not adding the multiple rows together where the Inv # & Fuel type match.

    Am I using the incorrect formula?

    I have also tried aggregate and subtotal to no avail.

    Thank you!

    1. Hello! It is very difficult to understand a formula that contains unique references to your workbook worksheets. However, note that INDEX MATCH always returns only the first value that matches the search criteria. To get the sum of multiple criteria, use the SUMIFS function or the SUMPRODUCT function.

  10. Hi,

    I am using a modified version of your

    INDEX MATCH with several criteria - formula example
    =INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

    I am not using the index portion as i don't need to find a specific index or return any value. I am just using the match portion MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0)) which returns the row that satisfies all of the criteria. This works for me when I paste it into an Excel cell. However, when I try to use the same formula in VBA i get a runtime error Type Mismatch on the lookup_array portion of the formula. each on the parenthesis like (G1=A2:A13) says Type Mismatch in the debugger.

    I know that all of my variables and things are right as it all works correctly when i just use the match function with basic values like (G1, A2:A13,0)

    Is there anyway to get around this?

  11. Hi there,
    Thanks for the share, very practical indeed.
    But I do find that it doesn't work for data extract from different tabs.
    =INDEX('Tab1'!A3:A54;MATCH(1,('Tab2'!D2>='Tab1'!F3:F54)*('Tab2'!D2=F3:F54)*(D2<=G3:G54),0)) then Ctrl+Shift+Enter works.

    Thanks,
    Aimee

  12. Hi, I am trying to match two conditions in a table against some data. What I am trying to do is: If, Column A of Table 1 matches Cell A2 in the raw data AND if, Column B of Table 1 also matches Cell G2 in the raw data, I would like it to return Match or 1. If Both Columns in table 1 does not match the two column in the raw data I would like to to return something else e.g no match, 0, N/A etc. I am just trying to separate the in the raw data, the ones that both match the two columns in the 2 coloums of the raw data.

    E.G
    Table 1:
    A | B |
    -----------------------------------------
    XYZ | ABC |
    ----------------------------------------
    BBB| CBA |
    -----------------------------------------
    RBA | IUW |

    RAW DATA:
    A | B | C | D | E| F |
    -----------------------------------------
    XYZ | 1 | 3 | Z |W| ABC | > This should match as column A and F match a row in table 1
    ----------------------------------------
    BBB| 1 | 3 | Z |W|IUW | > This should not match. Although column A AND F appears in table 1, Table 1 shows them in different rows rather than the same row
    -----------------------------------------
    BBB| 1 | 3 | Z |W|CBA | This should match as column A and F match a row in table 1

    Thank you

    1. Hi! If I understand your task correctly, use COUNTIFS function to find matches in two columns of different tables. The formula might look like this:

      =IF(COUNTIFS(Sheet1!A1:A20,Sheet2!A2,Sheet1!B1:B20,Sheet2!F2),"match","not match")

      I recommend paying attention to the Compare Sheets tool. It will help you quickly find and highlight differences between two Excel tables or sheets. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      1. Thank you Alexander for you super swift response!. It worked perfectly.

        I will take a look at the compare sheets tool.

        Thank you!!

  13. Greetings,
    I am having trouble combining the formulas IF and VLOOKUP. I have a table with Weight and Hight of different people. I also have a column with sex and age. I need the table to show me if they are in good weight/height. For sex, they are coded as 1 for male, and 2 for female. In another sheet (on a different tab), I have a growth chart for boys with Stature-for-age and Weight-for-age, wit data that shows in columns: "extremely low weight", "low wight", "normal", "overweight", and "obesity", and age group. I also have a third sheet on another tab with the same information for girls. So I need to enter the data, and the outcome i need is an x on the right column, according to the weight stature data entered, but i need to first consider if the sex data on that cell is 1 (for boys), to look in the table for boys sheet (tab), and if the value is 2, then look on the table for girls; then give me an x if the value is in the range on the chart, so i can see the growth status of this person.
    I also need to find values in other tables for different age groups, but by now ill be satisfied with finding data related to sex on charts for that age group, and their weight stature.
    Your help is highly appreciated!
    Thanks in advance

    1. Hi! If I understand your question correctly, you can use the IF function or the IFS function to do a condition search.
      =IF(A1=1, [INDEX MATCH formula 1], [INDEX MATCH formula 2])
      =IFS(A1=1, [INDEX MATCH formula 1], A1=2, [INDEX MATCH formula 2])

      Maybe this article will be helpful: VLOOKUP with IF statement in Excel

      1. correct.
        =IF(A1=1, look for and find the match values needed on sheet one,
        IF(A1=2, look for and find the match values needed on sheet two,
        if value enteres matches value/rank in selected sheet, then show an "x", otherwise, show " " (nothing)

  14. Please can you advise me - I'm not an Excel wiz.

    I previously ran a COUNTIFS query to identify the number of instances in my data where five criteria were met (returned 4 instances). I then wanted to know which instances met these five criteria, so I tried applying an INDEX MATCH (multiple criteria) formula. This successfully returned the first instance, but I want to know about the other three. How I do retrieve the second, third, and fourth instances?

    Formula used: {=INDEX(D2:D209, MATCH(1, (AI212=AF2:AF209) * (AI212=AJ2:AJ209) * (AI212=AN2:AN209) * (AI212=AP2:AP209) * (AI212=AS2:AS209), 0))}

    I also tried to using a VLOOKUP (multiple matches in multiple rows) formula (found here: https://www.ablebits.com/office-addins-blog/vlookup-multiple-values-excel/#vlookup-multiple-matches-rows), but the cell(s) shows up empty. Not sure where I'm going wrong (might be the 'm' and 'n', but I have no clue).

    Formula used: {=IFERROR(INDEX(D2:D209, SMALL(IF(1=((--(AI212=AF2:AF209)) * (--(AI212=AJ2:AJ209)) * (--(AI212=AN2:AN209)) * (--(AI212=AP2:AP209)) * (--(AI212=AS2:AS209))), ROW(D2:D209)-212,""), COLUMN()-34)),"")}

    I'd appreciate any guidance you could provide.

      1. Thanks for your prompt reply. I tried the suggested FILTER formula, and I was able to find the data I was looking for. Thank you!

        P.S. Just out of curiosity, is it possible to find second, third, nth instances using the first of the two formulae I tried (i.e., INDEX MATCH (multiple criteria))? It would be useful, because the INDEX MATCH formula specifies the return range whereas the FILTER formula pulls all data in the array, when it's only one column that is needed.

        1. Hi! In the FILTER formula, you can only get data from one column, for example FILTER(D2:D209, [criteria])

  15. Hi, I'm using Excel 2019. I'd like to index the column (L1 to L14) where either "NZ" or "N" or "E" or "RZ' first appears for a given row (1 to 4) - for example, Column L3 for row 1, Column L1 for row 2, column L3 for row 3, and column L5 for row 4. I would like to exclude K, R or Y.

    L1 L2 L3 L4 L5 L6 L7 L8 L9 L10 L11 L12 L13 L14
    1 Y Y NZ NZ NZ NZ N N NZ NZ NZ NZ Y Y
    2 NZ NZ NZ NZ NZ NZ N E E E E E
    3 K K N N NZ NZ Y Y Y Y K K
    4 R R Y Y RZ RZ Y Y RZ RZ N N

    Thank you very much.

    1. Hi! Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =INDEX(A1:F1,MATCH(1,(A1:F1<>"Y")*(A1:F1<>"K")*(A1:F1<>"R"),0))

      1. Thank you very much for your kind help. Mainly, I would like to identify the first column that contains either N, NZ, E or RZ in each row.

        1. Hi! You can get the column number using the MATCH function.

          MATCH(1,(A1:F1<>"Y")*(A1:F1<>"K")*(A1:F1<>"R")

          1. Hi, thank you for the advice BUT it has not solved the problem yet.

            Let me try to restate the issue:

            L1 L2 L3 L4 L5 L6 L7 L8 L9 L10 L11 L12 L13 L14
            Y Y NZ NZ NZ NZ N N NZ NZ NZ NZ Y Y
            NZ NZ NZ NZ NZ NZ N E E E E E
            K K N N NZ NZ Y Y Y Y K K
            R R Y Y RZ RZ Y Y RZ RZ N N

            The columns are labelled L1 to L14. I would like to index the first column in each row that contains either NZ, N, E or RZ. I have tried using {=INDEX($A$1:$N$1,MATCH(TRUE, ISNUMBER(SEARCH(A3:N3,"NZ")),0))} but it only gives the result for NZ. I would like to use this with multiple conditions to match either NZ, N, E or RZ and index the column that first contains that, within the same equation. Thanks

            1. Hi! Every new question you ask is different from the previous one. I'm not quite sure what you mean by "index the column". Try using this guide above. The formula might be something like this:
              INDEX(return_range, MATCH(1, INDEX((criteria1=range1) + (criteria2=range2) + (..), 0, 1), 0))

            2. I've also tried {=INDEX($A$1:$N$1, 1, MATCH(TRUE,ISNUMBER(SEARCH({"N","NZ","E","RZ"},A3:N3)),0))} BUT it still gives #N/A for all the rows except for the 2nd row where it correctly identifies L1.

              1. Here is one that seems to work BUT it still returns a result even for empty rows.

                {=INDEX($A$1:$N$1, 1, MIN(IF((ISNUMBER(SEARCH("N", A3:N3))) + (ISNUMBER(SEARCH("NZ", A3:N3))) + (ISNUMBER(SEARCH("E", A3:N3))) + (ISNUMBER(SEARCH("RZ", A3:N3))) > 0, COLUMN(A3:N3)-COLUMN(A3)+1, "")))}

                Please advise.

              2. To not show anything if no value is found, try this formula:

                =IFERROR(INDEX($A$1:$N$1, 1, MIN(IF((ISNUMBER(SEARCH("N", A3:N3))) + (ISNUMBER(SEARCH("NZ", A3:N3))) + (ISNUMBER(SEARCH("E", A3:N3))) + (ISNUMBER(SEARCH("RZ", A3:N3))) > 0, COLUMN(A3:N3)-COLUMN(A3)+1, -1))),"")

  16. I'm trying to return a specific value but having problems with the formula.

    I would like to return E based on looking up data in Column 1 to 4 but from inputting a one piece of data based on column 1-4 on different sheet.
    Basically, the I would input B but it would look through all the data in Column 1-4 and return E.

    EX.
    Column 1 / Column 2 / Column 3 / Column 4 / Column 5
    A B C D E
    F G H I J

      1. I tried to make an example but let's say like this

        I want to put answer in a specific cell based on info like this

        Columns are like this (named A, B, C, etc. for simplicity) and data is in rows below those columns (expressed as numbers)

        A B C D E X
        1 2 3 4 5 Y
        6 7 8 9 10 Z

        I want to have a separate cell if I put a number from columns A to E that it will look through all that data then return the info from in Y or Z. Hopefully this example makes sense. Appreciate the help in advance.

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

          =INDEX(F1:F5,MIN(IF(A2:E5=G1,ROW(A2:A5))))

          1. Definitely made progress with that but seems I'm still missing something that I'm trying to accomplish.

            Let me try this example:

            Top Row will be the column names and info will start on next row
            The top row is customer names and they all reference my internal item number (example below: XYZ and WXY is my number)
            The next row would be their personal customer product number number (express as 1, 2, 3.

            Customer A / Customer B / Customer C / Customer D / Product number
            1 / 2 / 3 / 4 / XYZ (our product number)
            5 / 6 / 7 / 8 / WXY (our product number)

            I want enter their product number in a complete different cell that will search all of that data to return our product number.
            Example: If I was to enter 4 then it would return XYZ
            Then if I can take it a step further in a cell beside it to reference which customer it would return (using above example then it would be Customer D

            There will be instances that our product number doesn't reference any of the numbers.

            Essential my two cell with the formulas would return
            I enter in first cell the number 4 then return XYP and in cell beside would say customer D.

            Does this make sense?
            I appreciate the help as I feel really close to getting the correct formula but something keeps throwing it out of whack.

  17. Hi, I have a question

    So I created a table including the origin, destination, shipment type, max weight and price. The shipment type has 2 types: box & pallet, the quantity of box is not included but for the quantity of pallet. the more quantities pallet have, the higher price it will be. I can use index and match to find out the price when I filter the origin & destination on the pivot table, but I can't figure out the pallet one since the quantity limits my imagination.

    For my formula of box one: =INDEX(Table2[PRICE], MATCH(1,(B2=Table2[ORIGIN])*(C2=Table2[DES]),0))

    Could you please assist me on the pallet one? It's better if you can combine these 2 formulas together, like writing a ifs statement.

    Thank you!

    1. Hi! Sorry, I do not fully understand the task. Unfortunately, this information is not enough to understand what you need. To understand what you want to do, give an example of the source data and the desired result.

  18. Hi

    I am struggling with an index and match

    The table of data would look a bit like the below

    I need to look up the data in table 1, lookup the data in column A and column B, look across the rows in table 2, from column C-E to find corresponding data from Column A table 1, then look at the value in table 1 column B, check if it is in between the range of min and max , and return the value corresponding to the value under column C-E for the data type within column A in table 1, and return the value to column C in table 1.

    Table 1 sheet 1is the input table, table 1 sheet 2 is the data table

    All help appreciated. thanks

    Table 1 (Sheet1)
    Column A Column B column C
    Nike 5 5.5
    NB 11 1.1

    Table 1(Sheet2)
    Column A Column B Column C Column D Column E
    Range Min Range Max Nike NB Adidas
    1 10 5.5 4.2 3.9
    11 20 3 .1 1.1 2.2

    1. Sorry, but we cannot offer assistance beyond the scope of this blog. The solution to your query requires a more comprehensive approach and cannot be addressed through a one formula. However, if you have a specific question about a function or formula, feel free to ask, and we will do our best to assist you. Also describe the result you want to get.

  19. Hello
    Can you help me to make consolidate of two different sheets data by using the same formula as you have shown above.

    1. Hi! I can't work with your worksheets. But I can give advice if you describe the problem and the formula.

  20. Using below table and knowing User ID and a Date that would fall somewhere between the Valid From and Valid Until looking to identify the Role at a given point in time.
    For example, for jsmith1 on 1/6/2022, want it to return Tech Agent

    Valid From Valid Until User ID Name Name Last/First Role Site
    11/4/2021 12/1/2021 jsmith1 John Smith Smith, John Agent Wichita
    12/1/2021 12/19/2021 jsmith1 John Smith Smith, John Agent Wichita
    12/19/2021 1/1/2022 jsmith1 John Smith Smith, John Agent Wichita
    1/1/2022 1/9/2022 jsmith1 John Smith Smith, John Tech Agent Wichita
    1/9/2022 1/16/2022 jsmith1 John Smith Smith, John Special Agent Wichita
    1/16/2022 1/30/2022 jsmith1 John Smith Smith, John Manager Wichita
    1/30/2022 2/1/2022 jsmith1 John Smith Smith, John Manager Wichita
    2/1/2022 2/6/2022 jsmith1 John Smith Smith, John Supervisor Wichita

    1. Hi!
      Have you tried the ways described in this blog post? Check the formula below, it should work for you:

      =INDEX(D1:D10,MATCH(1,(A1:A10<$K$1)*(B1:B10>$K$1)*(C1:C10="jsmith1"),0))

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

  22. Worked perfect. Thanks!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 :)