Hi,
I have the following table;
<tbody>
</tbody>
I then rank 1 to 5 from highest to smallest. Using =LARGE(B:B,1), =LARGE(B:B,2) and so on down to =LARGE(B:B,5)
According to the value of each ranked item in column E below, I need to then assign the person in that position. I get the following output;
<tbody>
</tbody>
I get Alex appearing 3 times in column F but need the name of the other 2 people that also have a value of 60 (i.e. Brad and Dave). I am using =INDEX($A$1:$A$5,MATCH(E2,$B$1:$B$5,1)) in column F above.
Without having to use VBA code, how can I solve this so that in rank 2, 3 & 4 I get the names Alex, Brad & Dave? These 3 names can appear in any order and doesn't have to be alphabetical. Also the source data range in terms of number of rows is dynamic.
Thanks
I have the following table;
A | B | |
1 | Alex | 60 |
2 | Brad | 60 |
3 | Chris | 50 |
4 | Dave | 60 |
5 | Elli | 70 |
<tbody>
</tbody>
I then rank 1 to 5 from highest to smallest. Using =LARGE(B:B,1), =LARGE(B:B,2) and so on down to =LARGE(B:B,5)
According to the value of each ranked item in column E below, I need to then assign the person in that position. I get the following output;
A | B | C | D | E | F | |
1 | Alex | 60 | Rank | |||
2 | Brad | 60 | 1 | 70 | Elli | |
3 | Chris | 50 | 2 | 60 | Alex | |
4 | Dave | 60 | 3 | 60 | Alex | |
5 | Elli | 70 | 4 | 60 | Alex | |
6 | 5 | 50 | Chris |
<tbody>
</tbody>
I get Alex appearing 3 times in column F but need the name of the other 2 people that also have a value of 60 (i.e. Brad and Dave). I am using =INDEX($A$1:$A$5,MATCH(E2,$B$1:$B$5,1)) in column F above.
Without having to use VBA code, how can I solve this so that in rank 2, 3 & 4 I get the names Alex, Brad & Dave? These 3 names can appear in any order and doesn't have to be alphabetical. Also the source data range in terms of number of rows is dynamic.
Thanks