+ Reply to Thread
Results 1 to 12 of 12

help for INDEX MATCH MAX formula

  1. 03-29-2021, 11:20 AM #1
    m2pk
    m2pk is offline
    Registered User
    Join Date
    03-29-2021
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft 365
    Posts
    6

    help for INDEX MATCH MAX formula

    Can anyone help with this please?

    I have the following formula which works perfectly fine

    I want to index a Text field from Column A in the "TASKPRED" sheet
    Criteria 1: Column D is "Not Started" OR "In-Progress"
    Criteria 2: Column B is equal to Cell A2 in the Active sheet
    Criteria 3: Column J is equal to "Y"

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    The problem is that there are 2 values that match the criteria above, and the formula is selecting a random one.
    I want to add another criteria which is to select the one with the Maximum date which is in Column P (TASKPRED!$P:$P).


    Thanks
    Attached Files Attached Files
    Last edited by m2pk; 03-29-2021 at 02:35 PM.
    Reply With QuoteRegister To Reply
  2. 03-29-2021, 11:21 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,635

    Re: help for INDEX MATCH MAX formula

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-29-2021, 11:42 AM #3
    m2pk
    m2pk is offline
    Registered User
    Join Date
    03-29-2021
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: help for INDEX MATCH MAX formula

    Thanks, but the attach button didn't work
    so I posted the link to the file above
    Reply With QuoteRegister To Reply
  4. 03-29-2021, 11:44 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,635

    Re: help for INDEX MATCH MAX formula

    Follow the instructions in the yellow banner at the top of the page - don't use the button (we know it doesn't work, which is why I directed you to the instructions in post #2 - please read them properly).

    I won't be following a link to a file-sharing site.
    Reply With QuoteRegister To Reply
  5. 03-29-2021, 12:24 PM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,635

    Re: help for INDEX MATCH MAX formula

    No response. Still waiting for you to attach that workbook, then I'll have a look for you.
    Reply With QuoteRegister To Reply
  6. 03-29-2021, 02:34 PM #6
    m2pk
    m2pk is offline
    Registered User
    Join Date
    03-29-2021
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: help for INDEX MATCH MAX formula

    Thanks AliGW, I just attached the file finally
    Reply With QuoteRegister To Reply
  7. 03-29-2021, 02:54 PM #7
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,169

    Re: help for INDEX MATCH MAX formula

    Try

    =IFERROR(INDEX(TASKPRED!$A:$A,MATCH(1,(((TASKPRED!$D:$D="Not Started")+(TASKPRED!$D:$D="In Progress"))>0)*(TASKPRED!$B:$B=A2)*(TASKPRED!$J:$J="Y")*(TASKPRED!$P:$P=MAX(TASKPRED!$P:$P)),0),1),0)

    Enter with Ctrl+Shift+Enter


    Change dates in column P to proper Excel dates (Always use proper dates not text or general formats)
    Reply With QuoteRegister To Reply
  8. 03-30-2021, 01:50 AM #8
    m2pk
    m2pk is offline
    Registered User
    Join Date
    03-29-2021
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: help for INDEX MATCH MAX formula

    Excellent John, this worked perfectly
    Now the problem that I faced is when I implemented this on my real spreadsheet which has 6232 rows on the TASKPRED sheet,
    the formula didn't work and gave "0",
    So I did a couple of tests
    1) test 1: deleted all the rows above the few rows that I showed you in the sample sheet, and it did not work.
    1) test 2: deleted all the rows below the few rows that I showed you in the sample sheet, and it worked, this doesn't make sense to me because the ones I deleted will never be matched with the criteria.
    Reply With QuoteRegister To Reply
  9. 03-30-2021, 02:51 AM #9
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: help for INDEX MATCH MAX formula

    Its quite ugly, but it might works:

    Please Login or Register  to view this content.
    Quang PT
    Reply With QuoteRegister To Reply
  10. 03-30-2021, 04:09 AM #10
    m2pk
    m2pk is offline
    Registered User
    Join Date
    03-29-2021
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: help for INDEX MATCH MAX formula

    wow, that's the longest formula that I've ever seen
    it did work and gave the same result as the formula I put in the main question.
    I was going to take it even if it's long if it gives me the result with maximum date
    Reply With QuoteRegister To Reply
  11. 03-30-2021, 04:32 AM #11
    PaulM100
    PaulM100 is offline
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: help for INDEX MATCH MAX formula

    Another way would be: 
    Please Login or Register  to view this content.
    Array entered. But you would need to have actual dates in P, not text.
    Click the * to say thanks.
    Reply With QuoteRegister To Reply
  12. 03-30-2021, 05:19 AM #12
    m2pk
    m2pk is offline
    Registered User
    Join Date
    03-29-2021
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: help for INDEX MATCH MAX formula

    thanks Paul,
    it's working but showin a different result.
    there are only 2 matching criteria with the formula in the question, and I wanted to select one with the max date,
    and your formula gave me a completely different result that isn't one of the 2 which is doesn't make sense to me
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index Match not cycling through entire index. Formula not updating when values change...
    By nordxnortheast in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2020, 10:44 AM
  2. Filter Sort Issue on Index Match (column with formula Index Aggregate)
    By SimChengKeng in forum Excel General
    Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  3. Index Match formula changing my Index daily
    By vitt4300 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2017, 02:19 PM
  4. [SOLVED] This formula works but I donīt understand why Index(Index) match
    By campelliann in forum Excel General
    Replies: 2
    Last Post: 01-25-2016, 05:55 PM
  5. [SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings
    By bkwins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  6. Index Match Index Formula work slow
    By avk in forum Excel General
    Replies: 9
    Last Post: 03-07-2012, 02:19 PM
  7. Index/Match/Match. Formula looking veritcal and horizontal headings to find a match
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-29-2012, 08:51 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1