look for text not using vlookup and sum if
 

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

look for text not using vlookup and sum if
 

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

--
Gary''s Student - gsnu200836

look for text not using vlookup and sum if
 

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




"Fanny" wrote:

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

look for text not using vlookup and sum if
 

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




"francis" wrote:

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




"Fanny" wrote:

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

look for text not using vlookup and sum if
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fanny" wrote:

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

look for text not using vlookup and sum if
 

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.


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in message
...
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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fanny" wrote:

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

look for text not using vlookup and sum if
 

Dear Helpers,

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

Fanny

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in message
...
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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fanny" wrote:

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