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$9999>60,$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 <(E-Mail Removed)>,
"toolbox" <(E-Mail Removed)> 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$9999>60,$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 <jemcgimpsey-(E-Mail Removed)>,
JE McGimpsey <(E-Mail Removed)> 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 <(E-Mail Removed)>,
"toolbox" <(E-Mail Removed)> 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))