I am struggling with the following MS-Excel formula. In the data that
follows I want to retrieve the NAME of the person who achieved the
highest test SCORE where the DIFFICULTY rating exceeded 60. From the
data below, I would expect to receive the name "Nate" from the 5th
row.

NAME DIFFICULTY SCORE
Nate 55 78
Sara 62 77
Sara 58 79
Nate 64 79
Sara 59 84

I've discovered that I can retrieve the relevant score (79) with the
following formula, but the same does not work for retreiving the text
value in column A. When I convert the formula to return column A
instead of C, I get the value "0" instead of "Nate."
=MAX(IF($B$2:$B$999960,$C$2:$C$9999)) /* enter formula then press
shift_control_enter */

Thanks for any help.

One way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(A2:A6,MATCH(MAX((B2:B6=60)*C2:C6),(B2:B6= 60)*C2:C6,FALSE))

In article .com,
"toolbox" wrote:

I am struggling with the following MS-Excel formula. In the data that
follows I want to retrieve the NAME of the person who achieved the
highest test SCORE where the DIFFICULTY rating exceeded 60. From the
data below, I would expect to receive the name "Nate" from the 5th
row.

NAME DIFFICULTY SCORE
Nate 55 78
Sara 62 77
Sara 58 79
Nate 64 79
Sara 59 84

I've discovered that I can retrieve the relevant score (79) with the
following formula, but the same does not work for retreiving the text
value in column A. When I convert the formula to return column A
instead of C, I get the value "0" instead of "Nate."
=MAX(IF($B$2:$B$999960,$C$2:$C$9999)) /* enter formula then press
shift_control_enter */

Thanks for any help.

Correction, should be rather than = in both places

In article ,
JE McGimpsey wrote:

One way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(A2:A6,MATCH(MAX((B2:B6=60)*C2:C6),(B2:B6= 60)*C2:C6,FALSE))

Thanks so much for your reply JE. The formula you provided works
great for me, but I have encountered one additional problem. The
formula contains the range "C2:C6", which is the current number of
rows in my table. I expect to add new rows to my table frequently.
However, if I alter the forumla to include rows that do not exist yet,
then the formula produces an error. Because the number of rows is
hardcoded in this formula, I would need to update the formula each
time I add a new row of data to the table. Can you think of a way to
either make the formula calculate the number of data rows to look at,
or to alter the formula so that it does not produce an error if it
encounters a blank row?

/* Work great assuming my last data is in row 6 */
=INDEX(A2:A6,MATCH(MAX((B2:B6=60)*C2:C6),(B2:B6= 60)*C2:C6,FALSE))

/* Produces an error when no data exists in rows 7 through 9999 */
=INDEX(A2:A6,MATCH(MAX((B2:B6=60)*C2:C9999),
(B2:B6=60)*C2:C9999,FALSE))

All the arrays should have the same number of rows:

=INDEX(A2:A9999, MATCH(MAX((B2:B9999=60)*C2:C9999),
(B2:B9999=60)*C2:C9999, FALSE))

In article om,
"toolbox" wrote:

Thanks so much for your reply JE. The formula you provided works
great for me, but I have encountered one additional problem. The
formula contains the range "C2:C6", which is the current number of
rows in my table. I expect to add new rows to my table frequently.
However, if I alter the forumla to include rows that do not exist yet,
then the formula produces an error. Because the number of rows is
hardcoded in this formula, I would need to update the formula each
time I add a new row of data to the table. Can you think of a way to
either make the formula calculate the number of data rows to look at,
or to alter the formula so that it does not produce an error if it
encounters a blank row?

/* Work great assuming my last data is in row 6 */
=INDEX(A2:A6,MATCH(MAX((B2:B6=60)*C2:C6),(B2:B6= 60)*C2:C6,FALSE))

/* Produces an error when no data exists in rows 7 through 9999 */
=INDEX(A2:A6,MATCH(MAX((B2:B6=60)*C2:C9999),
(B2:B6=60)*C2:C9999,FALSE))