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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  33. I wonder if it's possible to use the criteria from one sheet to search for information on a second chart.
    I want to see if the annual salary for each job title falls in the min, mid, or max range for each job level.
    Every I try gets errors.
    Chart 1
    JOB TITLE JOB LEVEL ANNUAL SALARY MARKET-RATIO
    Analytics Developer Junior $60,007.00
    Analytics Developer Lead $95,009.00
    Automation Tester Senior $95,009.00
    Business Analyst Intermediate $95,009.00
    Content Writer Senior $64,018.00
    Visual Designer Senior $110,016.00
    Visual Designer Senior $95,009.00

    Chart 2
    Job Title Level Min Mid Max
    Analytics Developer Junior 60,000 75,000 90,000
    Intermediate 70,000 87,000 104,000
    Senior 82,000 103,000 124,000
    Automation Tester Junior 56,000 70,000 84,000
    Intermediate 70,000 87,000 104,000
    Senior 79,000 99,000 119,000
    Business Analyst Junior 56,000 70,000 84,000
    Intermediate 68,000 85,000 102,000
    Senior 85,000 106,000 127,000
    Content Writer Junior 54,000 68,000 82,000
    Intermediate 64,000 80,000 96,000
    Senior 80,000 100,000 120,000
    Visual Designer Junior 56,000 70,000 84,000
    Intermediate 68,000 85,000 102,000
    Senior 85,000 106,000 127,000

    1. Hello!
      Please specify, what formula you used and what problem or error occurred.
      It’ll help me understand it better and find a solution for you.

  34. There are 2 sheets in which one column (alpha numeric value) is same in both the cases. The two sheets to be compared and give the value in column if both the cases are matching. The other column data to be called.
    Please help.

  35. Hi, i need some help using the below template formula:
    =INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

    it only returns one data instead of listing the rows with the same criterias met

    Below is my data example:

    *Sheet 1*(Raw data)-
    Column A: List of Coach Names (Not Unique)
    Column B: Student Name
    Column C: Tuition Day
    Column D: Tuition Time

    *Sheet 2*-
    Cell A1: Coach Name (e.g. Tom)
    Cell A2: Tuition Day (e.g. Monday)
    Cell A3: Tuition Time (e.g. 2pm)
    Cell B1(Column B): Formula Output list of cells in column B that match criterias.

    Criteria is- Show and list all student name if below 3 criterias are met:
    1) Column A match with cell A1 (Coach Name)
    2) Column C match with cell A2 (Tuition Day)
    3) Column D match with cell A3 (Tuition Time)

    In Sheet 2: cell A1, A2, A3, the content of the cell is changed daily manually.
    I want Sheet 2: cell B1 column B to use formula to list all student names that match the criterias.

    Really appreciate any assistance and insights! :)

  36. dear Sir/Mam,
    i want to know if data is in 3 cell & data value is also in 3 same down side cell i want find data which is in three cell and give result sum of these data value...

    data 16 18 20 22 24 26
    value 240 240 240 240 240 240

    data 16-18-20
    sum 720

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

      =IF(SUM(--(A1:C1<>""))=3,SUM(A2:C2),"")

      Hope this is what you need.

      1. NO SIR ITS NOT WORKING WHICH I LIKE THIS TYPE....
        COLOUM DATA 1 2 3 16 18 20 22 24
        SUM VALUE 50 500 120 240 240 240

        COLOUM DATA SUM VALUE
        1-2-3 670
        2-3-16 860
        3-18-20 600

  37. I'm developing a 15 x 15 matrix of results, and looking to return the column and row numbers for the MAX result in the 15 x 15 range ... any suggestions?

    1. Hello!
      If your array of values is located in the range A1: O15, then the row number with the maximum value can be found by array formula

      =MIN(IF(A1:O15=MAX(A1:O15),ROW(A1:A15)))

      column number -

      =MIN(IF(A1:O15=MAX(A1:O15),COLUMN(A1:O1)))

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

      1. Thanks for the advice ... really helped me out

  38. I'm looking for something that gives me joined text.
    List is like 1) Name 2)Work Done 3)Amount 4)Month
    Output list is like
    1)Month Name
    2)Sum of Amount for Month Given
    3)Textjoin of Workdone for Month Given

    Any help ??

    1. use textjoint formula for this and textjoint formula avilable on msoffice 365 or u can use some vba codes for this also .....for more about it send me excel file on my mail...

  39. Hi, I have a query. I have a data matrix and some of the cells are filled with Y, where the Row header and Column header combination is active. For every value Y, I need to perform a vlookup in another sheet using the corresponding row and column header values. In the example below, I need to replace the Ys with the number from 2nd sheet. Any suggestions and help is appreciated.
    SHeet 1 -
    AA BB CC DD
    A Y
    B Y
    C Y
    D Y
    Sheet 2 -
    A AA 1
    B BB 2
    C CC 3
    D DD 4

    1. SHeet 1 is :

      AA BB CC DD
      A Y
      B Y
      C Y
      D Y

  40. What If I have multiple output upon multiple criteria? Then how this formula would be?

    1. Hello!
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  41. Hello! Thanks for the great article. Can the Index/Match formula contain a cell value that also contains an index/match formula? I have the following: S2 contains =Index(Rep_ID, Match(A2, OrderNumber,0)) - (rep_ID and OrderNumber are from AcctsList sheet);
    T2 contains =Index(SalesRepName, Match(S2,SalesRepID, 0))- (SalesRepName and SalesRepID are from Slsp Sheet).
    T2 returns #N/A unless I change S2 to the value of the formula and I don't want to have to create another column to paste values. Is there a way to combine the formulas in T2 so that it will provide the RepName needed?
    Extensive search and forum request has yielded no answers.
    Any help is greatly appreciated!
    Thank you,
    Phisaw

    1. Hello!
      I can not check the work of formulas, because I do not have your data. But you can try using this formula

      =Index(SalesRepName, Match(Index(Rep_ID, Match(A2, OrderNumber,0)),SalesRepID, 0))

  42. I have to apologize. It works just fine with Office 365, but when I try on the work computer which is running Office 2013 I get the error.
    I assume the below info are what the references are, correct?

      1. Found one.
        In some cases, a customer conducts multiple events at a given facility on the same day. We want to just list each customer once so want to remove duplicates if possible. Not sure if I would have to split out the AllData sheet into separate sheets/tables to make it easier.
        I really appreciate all the help Alexander.

  43. I have been stuck trying to get cell content based on row and column matches. I have one worksheet that contains base data of 3 columns (A = Dates, B = Facilities, C = Customers). On worksheet 2 we want to display the information in a linear calendar style that includes each day of a year. The dates are across the top row starting at Column B. Column A is a list of facilities. We want to put the customer into the appropriate cell within the correct facility row and under the correct date(s) column(s). I have tried using pivot tables but I just end up with a count of customers and not the actual customer name in the given cell.
    In most cases there is only ever just one customer per facility per day. But on occasion one may just have part of a day and another may have the evening portion. In those cases just listing both with a separator ? or a - would serve our purpose.

    I am sure there is a way to do this but looking all over the place and asking have not yielded an answer.

    1. Hello!
      On Sheet2 in cell B2, write down the formula.
      =CONCAT(IFERROR(INDEX(Sheet1!$C$2:$C$20, SMALL(IF(Sheet2!B$1&Sheet2!$A2=Sheet1!$A$2:$A$20&Sheet1!$B$2:$B$20, ROW(Sheet1!$C$2:$C$20)-1), ROW(INDIRECT("1:"&ROWS(Sheet1!$C$2:$C$20)))))&"-",""))
      Then copy it down the column.
      Sheet1!$C$2:$C$20 - Customers
      Sheet1!$A$2:$A$20 - Dates
      Sheet1!$B$2:$B$20 - Facilities
      Sheet2!B$1 - Date
      Sheet1!$A$2:$A$20 - Facilities

      If there is anything else I can help you with, please let me know.

      1. Alexander thanks for the help. When I plug in the formula, I get a #NAME? error and the highlighted in red and blue part of the formula is this part Sheet2!B$1&Sheet2!$A2

  44. Great article - thanks.
    In your experience, which is the fastest/most efficient approach when there is a lot of data?

    1. Hello Tom,

      In my experience, when working with huge bulks of data VLOOKUP is the slowest and most problematic one. So, I'd rely on either INDEX MATCH (works in any version) or XLOOKUP (works in Excel 365 only).

  45. Hi and thank you for such a wonderful post.
    I am unable to understand how the formula has returned 3 from below expression.
    {1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}
    Appreciate your help.

    1. Hello Enan!
      If you perform mathematical operations with the logical values TRUE and FALSE, then Excel turns them into numbers 1 and 0.
      I hope I answered your question.

      1. Dear Alexander, Thank you for your response.
        i am unable to understand. I want to know how this 3 comes as shown below.
        =INDEX(D2:D13, 3)
        What i understood is:
        ={1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}
        ={1}*{1}*{1}
        =1
        Then how come it is coming 3.
        Appreciate your help.

  46. Hi,
    How do I put in the formula if one of the criteria is in a range of numbers e.g. 20 - 29?
    E.g. 1st criteria is age, 2nd criteria is exercise time: 10-15 mins,
    so results is if age 20 exercises 12 mins = normal
    if age 20 exercises 9 mins = weak
    exercises 18 mins = strong, etc

    1. Hello Lynn!
      Please use the example above: "INDEX MATCH with several criteria - formula example".
      Please enter age in column A, exercise time – in column B, and estimation – in column D.
      Fill in the table with all possible age variants and exercise time with the corresponding estimation.
      After that, you will search for a necessary row in this table using the recommended formula.

      If you still have any questions, I will be happy to help you further.

  47. 1)If= first month(1 sep 2019 to 2 feb 2020) all floors commission 2%
    2)If= second month (3 feb 2020 to 29 Feb 2020) floor wise commission
    Lower floor - 3%
    Middle floor - 3.5%
    Higher floor - 4 %
    3) if = third month ( 1 March 2020 to 31 march 2021) onwards floor wise commission
    Lower floor - 2 %
    Middle floor - 2.5%
    Higher floor -3%
    AND
    3 TYPES OF SOURCE(X,Y,Z)
    Y SOURCE ELEGIBLE ONLY 2% ALL TYPES OF CONDTION

    CAN YOU PLEASE HELP WHICH FORMULA WORKING IN 1 CELL excel...

  48. why should not try like this type
    INDEX(E3:E14,MATCH(TRUE,COUNTIFS(B3:B14,H2,C3:C14,H3,D3:D14,H4),0))
    INDEX(E3:E14,MATCH(1,(--(COUNTIFS(B3:B14,H2,C3:C14,H3,D3:D14,H4))),0))

    1. Hi Kanth,

      Thank you for your feedback.

      These formulas won't work. The point is that COUNTIFS returns the count of cells for which all the criteria are TRUE, while the MATCH function needs a lookup array (not a count!) in which it can find the row(s) that meet all the criteria and pass the relative position of the first found row to INDEX.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)