Forum Discussion
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 longest waiter and displaying their surname (table is called Apr24Apr25 and the PW column calculates the total wait times in days).
=INDEX(Apr24Apr25[Forename],MATCH(MAX(Apr24Apr25[PW]),Apr24Apr25[PW],0))
However now I want it to look at a column called Status (Apr24Apr25[Status]) and only look at the ones that say "Waiting". Any ideas?
I always work by building the parts of a formula separately then putting them together, this is actually part of a bigger formula, I'll paste it below in case it's important.
=DATEDIF(0,AC623,"y")&" years " &DATEDIF(0,AC623,"ym")&" months "&DATEDIF(0,AC623,"md")&" days"&" ("&INDEX(Apr24Apr25[Forename],MATCH(MAX(Apr24Apr25[PW]),Apr24Apr25[PW],0))&" "&INDEX(Apr24Apr25[Surname],MATCH(MAX(Apr24Apr25[PW]),Apr24Apr25[PW],0))&", "&INDEX(Apr24Apr25[Location],MATCH(MAX(Apr24Apr25[PW]),Apr24Apr25[PW],0))&", row "&INDEX(ROW(Apr24Apr25),MATCH(MAX(Apr24Apr25[PW]),Apr24Apr25[PW],0))&", referred for "&INDEX(Apr24Apr25[Referred For],MATCH(MAX(Apr24Apr25[PW]),Apr24Apr25[PW],0))&")"
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")))
- Thanks Hans
- 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)))
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)))
