CODEWITHSUNDEEP
excel-formula
Sid. T.
Sid. T.

Reputation: 93

Index-Match function with NOT EQUAL criteria

I am trying to use the index with match function to give me the order of people based on their rankings. My data is something like this:

     A         B          C        
1   Rank     Name      Score                   
2 
3    1        Joe        100%                    
4    3        Bob        80%
5    1        John       100%
6    2        Dan        90%
7
8   RankOrder Name
9    1        =index(b3:b6,match(1,a3:a6,-1))    Result = Joe
10   1        =index(b3:b6,match(1&<>"b9",a3:a6,-1))   Result = John  **HELP 
11   2        =index(b3:b6,match(1&<>"b9&b10",a3:a6,-1)) Result = Dan **HELP

The first formula is to find who has a ranking of 1 or greater, then the second and third formula is where I'm struggling. I need to find who the has the next ranking closest to 1 but has not already been outputted in cell B9. Then the next formula would be the same but not someone that's been outputted in cell B9 & B10. Hopefully this make sense.Thanks!

Upvotes: 0

Views: 3004

Answers (1)

user4039065
user4039065

Reputation:

Use index and aggregate with countif for aggregate's k factor.

=INDEX(B$1:B$6,AGGREGATE(15,6,ROW($3:$6)/(A$3:A$6=A9),COUNTIF(A$9:A9,A9)))

enter image description here

Upvotes: 1

Related Questions