Forum Discussion

Kirk-Anderson's avatar
Kirk-Anderson
Copper Contributor
Jun 25, 2024
Solved

INDEX MATCH with MAXIFS?

Hey all. I'm working on some waiting lists, I'm a bit stuck on a formula that indentifies the longest waiter in a table who is still waiting.   I've got the first bit working, identifying the longe...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 25, 2024

    Kirk-Anderson 

    Try

     

    =INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[Status], "Waiting"))*(Apr24Apr25[Status]="Waiting")))

HansVogelaar's avatar
HansVogelaar
MVP
Jun 25, 2024

Kirk-Anderson 

Try

 

=INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[Status], "Waiting"))*(Apr24Apr25[Status]="Waiting")))

Kirk-Anderson's avatar
Kirk-Anderson
Copper Contributor
Jul 14, 2024

Hey HansVogelaar, I'm now trying to edit the formula to look at referrals from the last year rather than still waiting. I've tried substituting the Apr24Apr25[Status], "Waiting" for Apr24Apr25[CAMHS NDC Referral Date],">"&TODAY()-I760 with I760 holding the number of days back the user wants to go (e.g. 31 for a month, 365 for a whole year). This works for all the other formulae on the tab, but this one is not happy. What I'm trying unsuccessfully is 

=INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[CAMHS NDC Referral Date],">"&TODAY()-I760))*(Apr24Apr25[CAMHS NDC Referral Date],">"&TODAY()-I760)))

It's happy with it before the * but not after. Any help appreciated! 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 14, 2024

    Kirk-Anderson 

    It should be

     

    =INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[CAMHS NDC Referral Date],">"&TODAY()-I760))*(Apr24Apr25[CAMHS NDC Referral Date]>TODAY()-I760)))

Resources