look for text not using vlookup and sum if

[Fanny]

Dear Helpers,

I have a long listing below for checking

Name Score
Peter 70
Anna 120
Sam 50
........


I use Max to find the highest score is 120 and I want to use the highest
score to look for the name who gets the highest score.

How to do so,

thanks a lot for your help.

Fanny

 

[Gary''s Student]

=OFFSET(A1,MATCH(MAX(B:B),B:B)-1,0)

 

[francis]

Assuming your MAX formula is in C2

place this classic formula in D2
=INDEX(A2:A10,MATCH(C2,B2:B10,0))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[francis]

you may try this without using a separate cell for the MAX formula in C2
described below

=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Shane Devenshire]

Hi,

If you have titles in the top row then change GS's to

=OFFSET(A1,MATCH(MAX(B:B),B:B,)-1,0)

or shorten the other suggestion to

=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10))

or try this instead:

=LOOKUP(MAX(B2:B9),B2:B9,A2:A9)

If you have a tie for the MAX these formula will give different results.

 

[T. Valko]

or shorten the other suggestion to

=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10))
or try this instead:
=LOOKUP(MAX(B2:B9),B2:B9,A2:A9)

And both of those will fail if the data was like this:

Name Score
Anna 120
Peter 70
Sam 50

It's just "dumb luck" that they work on the sample as posted.

Quality trumps quantity.

 

[Fanny]

Dear Helpers,

Thank you very much for your advice and I have solved my problem.

Fanny