I have a table in Cells A1:M11. Cells B1:M1 contain Jan thru Dec.
Cells A2:A11 contain team numbers 1 thru 10. The table is populated by
scores. (one score for each team for each month.

What I want to do elswhere on the sheet in three seperate cells) is
show the Maximum score, the Team name and the Month the Maximum score
was achieved.

The max score is no problem. For team name and month, I will use
VLOOKUP formulae refering to two seperate charts. But to use the
VLOOKUP formulas, I need to ascertain the Row() and Column() for the
Maximum score.

I'd like to be able to do this without creating a bunch of helper cells
but don't know how. If I try to use the expression "=ROW(MAX(A1:M11))"
in a VLOOKUP formula I get a message saying there is a mistake in the
formula.

Does anyone know a way to get what I want without creating intermediate
"helper cells"?


--
ElsiePOA


------------------------------------------------------------------------
ElsiePOA's Profile: http://www.excelforum.com/member.php...fo&userid=3901
View this thread: http://www.excelforum.com/showthread...hreadid=556150

Hi!

Each of these formulas are array formulas. They MUST be entered using the
key combination of CTRL,SHIFT,ENTER:

For the team name:

=INDEX(A2:A11,MAX((ROW(B2:M11)-ROW(B2)+1)*(B2:M11=MAX(B2:M11))))

For the month:

=INDEX(B1:M1,MAX((COLUMN(B2:M11)-COLUMN(B2)+1)*(B2:M11=MAX(B2:M11))))

Note: if there is more than one instance of MAX this will not work properly.
In that case you will have no choice but to use helper cells!

Biff

"ElsiePOA" wrote in
message ...

I have a table in Cells A1:M11. Cells B1:M1 contain Jan thru Dec.
Cells A2:A11 contain team numbers 1 thru 10. The table is populated by
scores. (one score for each team for each month.

What I want to do elswhere on the sheet in three seperate cells) is
show the Maximum score, the Team name and the Month the Maximum score
was achieved.

The max score is no problem. For team name and month, I will use
VLOOKUP formulae refering to two seperate charts. But to use the
VLOOKUP formulas, I need to ascertain the Row() and Column() for the
Maximum score.

I'd like to be able to do this without creating a bunch of helper cells
but don't know how. If I try to use the expression "=ROW(MAX(A1:M11))"
in a VLOOKUP formula I get a message saying there is a mistake in the
formula.

Does anyone know a way to get what I want without creating intermediate
"helper cells"?


--
ElsiePOA


------------------------------------------------------------------------
ElsiePOA's Profile:
http://www.excelforum.com/member.php...fo&userid=3901
View this thread: http://www.excelforum.com/showthread...hreadid=556150

http://cjoint.com/?gBvdMqL6nx

Month:
=INDEX(Mois,MAX(IF(Score=MAX(Score),COLUMN(Score)) )-COLUMN(Mois)+1)
Validate with Shilt+Ctrl+Enter

Number:

=INDEX(Nombre,MAX(IF(Score=MAX(Score),ROW(Score)))-ROW(Nombre)+1)
Validate with Shilt+Ctrl+Enter

Cordialy JB

ElsiePOA a écrit :

I have a table in Cells A1:M11. Cells B1:M1 contain Jan thru Dec.
Cells A2:A11 contain team numbers 1 thru 10. The table is populated by
scores. (one score for each team for each month.

What I want to do elswhere on the sheet in three seperate cells) is
show the Maximum score, the Team name and the Month the Maximum score
was achieved.

The max score is no problem. For team name and month, I will use
VLOOKUP formulae refering to two seperate charts. But to use the
VLOOKUP formulas, I need to ascertain the Row() and Column() for the
Maximum score.

I'd like to be able to do this without creating a bunch of helper cells
but don't know how. If I try to use the expression "=ROW(MAX(A1:M11))"
in a VLOOKUP formula I get a message saying there is a mistake in the
formula.

Does anyone know a way to get what I want without creating intermediate
"helper cells"?


--
ElsiePOA


------------------------------------------------------------------------
ElsiePOA's Profile: http://www.excelforum.com/member.php...fo&userid=3901
View this thread: http://www.excelforum.com/showthread...hreadid=556150

Biff
Thanks, that works great.
ElsiePOA


--
ElsiePOA


------------------------------------------------------------------------
ElsiePOA's Profile: http://www.excelforum.com/member.php...fo&userid=3901
View this thread: http://www.excelforum.com/showthread...hreadid=556150

You're welcome. Thanks for the feedback!

Biff

"ElsiePOA" wrote in
message ...

Biff
Thanks, that works great.
ElsiePOA


--
ElsiePOA


------------------------------------------------------------------------
ElsiePOA's Profile:
http://www.excelforum.com/member.php...fo&userid=3901
View this thread: http://www.excelforum.com/showthread...hreadid=556150