Forum Discussion
INDEX MATCH with MAXIFS?
Try
=INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[Status], "Waiting"))*(Apr24Apr25[Status]="Waiting")))
Try
=INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[Status], "Waiting"))*(Apr24Apr25[Status]="Waiting")))
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!
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)))
Thanks HansVogelaar!
- Hi Hans, is there a way to add a second column and value in? I have a column called Location with five selectable values (one of which is "Swindon"), so I would be wanting to identify the longest waiter from Swindon for example. Thanks in advance.
Let's say you enter the location you want to focus on in L2. The formula for longest waiter from that location is
=INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[Status], "Waiting", Apr24Apr25[Location], L2))*(Apr24Apr25[Status]="Waiting")*(Apr24Apr25[Location]=L2)))
- That's so helpful, thanks again Hans.
- Thanks Hans
