I have a sheet which I point failures, it is divided by failures (rows) and days (columns), I point the quantity of failures by the type of failure that day. So I used the vlookup to show me the top 3 failures of each day, but there is a problem, when it has the same quantity of different failures, it shows the same failure, not the next one with the same quantity.
I've tried ways to correct this, but everything I found didn't work, because it has variable quantity depending the day, and what I found use the COUNTIF trick, I can't define a value, so it doesn't work.
can anyone help me please?
There's probably an easier way, but this formula should work:
=LET(day,Table1[Sunday],myarray,FILTER(CHOOSE({1,2},Table1[Failure],day),RANK.EQ(day,day)<=3),SORTBY(myarray,INDEX(myarray,,2),-1))
As you can see it returns an dynamic array containing the top 3 Failures in descending order, along with the number of failures.
This should account for any ties as you can see in my example. However, it is possible for the returned array to be more than 3 rows if there is a tie for third place. Not sure if that's desirable or not.