Forum Discussion
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...
Try
=INDEX(Apr24Apr25[Forename], XMATCH(1, (Apr24Apr25[PW]=MAXIFS(Apr24Apr25[PW], Apr24Apr25[Status], "Waiting"))*(Apr24Apr25[Status]="Waiting")))
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.