Return cell reference rather than value

[Suzy]

MIN and MAX return the value. I would like to return the cell reference
containing that value. For example MAX(B2:CH555) returns 7580. I would like
it to return M350. Or better yet, the value in A350, the player!

Application: Scores for our group. Names appear in ColumnA. Dates we
played in Row1. Individual scores in B2:CH555. A couple of extra rows and
columns for averages. I want to know WHO had the highest score for the year,
who had the lowest, etc.

The tedious way is to use MIN and MAX, then CNTRL-F to find the returned
value.

Thanks for solving the puzzle.

 

[Bernard Liengme]

Silly typos:
=INDEX(A2:A555,MATCH(MAX(CJ2:CJ555),CJ2:CJ555,0))
sorry!

 

[T. Valko]

Individual scores in B2:CH555.

That's a lot of cells. In all of those cells, is there only 1 cell that
contains a max and /or min score?

Try these array formulas** :

Max score:

=INDEX(A:A,MAX((B2:CH555=MAX(B2:CH555))*ROW(B2:CH555)))

Min score:

=INDEX(A:A,MIN(IF(B2:CH555=MIN(B2:CH555),ROW(B2:CH555))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Suzy]

I'm a bit over my head with arrays, but this is the opportunity to learn!

I don't understand what the asterisk does in the MAX array.

I don't understand the IF in the MIN formula, but not in the MAX.

Thanks for your patience with me.

 

[Suzy]

Excellent! Worked great. And I can adapt it for lowest score with MIN.

As to who came second, I can delete the max score, then rerun.

Thanks.