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))&”)”

​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))&”)”     Read More

​