Show ranking according to lookup

[itfmm]

Hello,

I have a table that tracks players' achievement scores based on hours of practice: player names M1:S1, hours of practice L2:L30.

In cell V2, I enter the number of hours of practice. Using that figure in a lookup, I need to generate a ranking of the players in cells V5:AB5. Right below that, I need to see how far back each player is from the leader.

Here's the other part of it, the hours in column L are not consecutive, meaning it will look something like this 5,7,10,12,15,20,30,45,60, etc. If I enter 34 as the hours of practice, the ranking needs to be based on scores in the 45-hour row.

This is an example of how I need my results to calculate (the table is in descending order only because I have been trying to use INDEX MATCH with -1 match type):

Thanks so much in advance!

 

[Marcelo Branco]

If is not possible two players with the same score (tie) try this

Formula in W2 (helper cell to get the MAX)
=MAX(INDEX(M2:S19,MATCH(V2,L2:L19,-1),0))

Formula in V5 copied across till AB5
=INDEX($M$1:$S$1,MATCH(LARGE(INDEX($M$2:$S$19,MATCH($V2,$L$2:$L$19,-1),0),COLUMNS($V6:V6)),INDEX($M$2:$S$19,MATCH($V2,$L$2:$L$19,-1),0),0))

Formula in V6 copied across
=LARGE(INDEX($M$2:$S$19,MATCH($V2,$L$2:$L$19,-1),0),COLUMNS($V6:V6))-$W$2

Hope this helps

M.

 

[itfmm]

Oh, this is absolutely perfect!

Two questions;

What would happen if a tie in scores were to occur? Also, in the second and third formulas, what is the "COLUMNS(V6:V6)" function doing?

Thanks so much, Marcelo! You're a fantastic person!

 

[Marcelo Branco]

First question: dealing with ties
We need a slightly different solution - something like this (in fact this is a more robust solution)
Observe the values in red (a tie)


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[TD]

Q​

[/TD]
[TD]

R​

[/TD]
[TD]

S​

[/TD]
[TD]

T​

[/TD]
[TD]

U​

[/TD]
[TD]

V​

[/TD]
[TD]

W​

[/TD]
[TD]

X​

[/TD]
[TD]

Y​

[/TD]
[TD]

Z​

[/TD]
[TD]

AA​

[/TD]
[TD]

AB​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Pr. Hours​

[/TD]
[TD]

Max​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

170​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

34​

[/TD]
[TD]

101,18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

160​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

150​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1st​

[/TD]
[TD]

2nd​

[/TD]
[TD]

3rd​

[/TD]
[TD]

4th​

[/TD]
[TD]

5th​

[/TD]
[TD]

6th​

[/TD]
[TD]

7th​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

140​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Name​

[/TD]
[TD]

G​

[/TD]
[TD]

A​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

D​

[/TD]
[TD]

C​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

130​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Score​

[/TD]
[TD]

101,18​

[/TD]
[TD]

100,998​

[/TD]
[TD]

100,998​

[/TD]
[TD]

100,961​

[/TD]
[TD]

100,947​

[/TD]
[TD]

100,922​

[/TD]
[TD]

100,839​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

120​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Diff​

[/TD]
[TD]

0​

[/TD]
[TD]

-0,182​

[/TD]
[TD]

-0,182​

[/TD]
[TD]

-0,219​

[/TD]
[TD]

-0,233​

[/TD]
[TD]

-0,258​

[/TD]
[TD]

-0,341​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

110​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

75​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

60​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

45​

[/TD]
[TD]

100,998​

[/TD]
[TD]

100,839​

[/TD]
[TD]

100,922​

[/TD]
[TD]

100,947​

[/TD]
[TD]

100,998​

[/TD]
[TD]

100,961​

[/TD]
[TD]

101,18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Same formula in W2
=MAX(INDEX(M2:S19,MATCH(V2,L2:L19,-1),0))

Formula in V6 copied across
=LARGE(INDEX($M$2:$S$19,MATCH($V2,$L$2:$L$19,-1),0),COLUMNS($V6:V6))

Formula in V7 copied across
=V6-$W$2

Array formula in V5 copied across
=INDEX($M$1:$S$1,SMALL(IF(INDEX($M$2:$S$19,MATCH($V2,$L$2:$L$19,-1),0)=V6,COLUMN($M$2:$S$19)-COLUMN($M$2)+1),COUNTIF($V6:V6,V6)))

confirmed with Ctrl+Shift+Enter, not just Enter


Second question
COLUMNS($V6:V6) returns the number of columns of the range $V6:V6, i.e., 1
So the formula in V6 becomes
=LARGE(INDEX($M$2:$S$19,MATCH($V2,$L$2:$L$19,-1),0),1)
and returns the largest value

When the formula is copied across COLUMNS($V6:V6) becomes COLUMNS($V6:W6), COLUMNS($V6:X6), COLUMNS($V6:Y6) ...., returning 2, 3, 4....
So the formulas in W6, X6, Y6 result in the 2nd largest, 3rd largest, 4th largest respectively

To see what each formula does, step by step, try Formulas > Evaluate formula

M.

 

[itfmm]

Marcelo, thank you so much! This worked exactly as needed. Thanks for the explanation as well.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.