Ranking search

[Guest]

Dear All,

I wan a formula to "detect" who have the highest mark amount the whole
score table.

Test 1 Test 2
Jo 56 55
Mark 98 06
Simon 89 57
Stella 88 58
Jean 50 51

I wan a Formula to show:

Highest score: Mark, Test 1
Lowest score: Mark, Test 2

 

[Domenic]

Assumptions:

A2:A6 contains the name

B1:C1 contains the column header/label

B2:C6 contains the data

Formula:

Let E2 contain 'Highest Score:' (just a label)

F2:

=INDEX(A2:A6,MIN(IF(B2:C6=MAX(B2:C6),ROW(B2:C6)-ROW(B2)+1)))&",
"&INDEX(B1:C1,MATCH(MAX(B2:C6),INDEX(B2:C6,MIN(IF(B2:C6=MAX(B2:C6),ROW(B2
:C6)-ROW(B2)+1)),0),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the lowest
score, replace MAX with MIN.

Hope this helps!

 

[Dave Peterson]

This will show the first person with the highest score in test1:
=index($a$2:$a$999,match(max($b$2:$b$999),$b$2:$b$999,0))

First person with the lowest score in test1:
=index($a$2:$a$999,match(min($b$2:$b$999),$b$2:$b$999,0))

Change the range to match (I went through row 999) and change the column
reference for the other tests.

 

[T. Valko]

Ties?

Biff

Assumptions:

A2:A6 contains the name

B1:C1 contains the column header/label

B2:C6 contains the data

Formula:

Let E2 contain 'Highest Score:' (just a label)

F2:

=INDEX(A2:A6,MIN(IF(B2:C6=MAX(B2:C6),ROW(B2:C6)-ROW(B2)+1)))&",
"&INDEX(B1:C1,MATCH(MAX(B2:C6),INDEX(B2:C6,MIN(IF(B2:C6=MAX(B2:C6),ROW(B2
:C6)-ROW(B2)+1)),0),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the lowest
score, replace MAX with MIN.

Hope this helps!

 

[Domenic]

Ties?

Biff

A different approach would be necessary...

 

[T. Valko]

This OP posted a similar question last night but didn't explain it very
well. (at least, to where I could understand what they wanted)

Biff

 

[Domenic]

Yeah, I noticed it after posting my solution. It seems that half the
battle is trying to understand the question...

 

[Guest]

Domenic,

Formula works. If I would like to ranking them accordingly to 1st, 2nd &
3rd. How would I go abt?