Simple VLOOKUP with NON ascending order

[Duane]

Morning folks: I am trying to do something very simple but I just can't seem
to get it to work the way I want! I have been searching for an answer for 2
days now!!!

On my STATS page I have a list of basketball players down a column (taken
from TEAM LIST page) and the next columns have their POINTS, REBOUNDS,
ASSISTS, etc. (taken from GAMES DATA page)
At the bottom, I want to see who has the most points: '=MAX(B3:B30)' and I
would like to show that players name next to the number returned from MAX.
The same for '=MIN(B3:B30)'
I think my problem is that player #1 may have a higher number than Player#23
and my calculation returns 'N/A', am I right thinking this? Am I getting
caught on some restrictions I don't know about?

Thanks for all your help!
Regards;
Duane in Nova Scotia

John 4 6 2
Sean 12 2 1
Todd 0 2 1
Andy 6 1 0
Brad 23 4 8
Vern 2 0 0
Mark 8 4 2

 

[Mike H]

Hi,

Is this what you want

=INDEX(A3:A30,MATCH(MAX(B3:B30),B3:B30,FALSE),1)

change MAX to MIN for the minumum assists etc
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Don Guillett]

One way. Change the columns to suit
=INDEX(H:H,MATCH(MAX(I:I),I:I,0))

 

[Gary''s Student]

With your data in A1 thru D7:
=INDEX(A:A,MATCH(MAX(B:B),B:B))
returns Brad

 

[Duane]

Thank you so much! I was caught up on making a VLOOKUP work that I didn't
think of other possibilities!

Greatly appreciated!

 

[Mike H]

Glad I could help.

Vlookup looks in the first column of data and returns a column to the right
so couldn't have worked on this data layout
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Duane]

HA!! Now I have another little glitch in my calculations!!!
I have the ability to have 15 boys on the team but we currently have only 12
so when I want to find the player with the 'MIN assists' it shows me the
entry where I don't have a boy because 'HE' (not listed) is '0'

I need some way to have my calculation IGNORE the entries if the Player name
is a NUL non entry.
Sorry if I am wasting your time but I am not that fluent with Excel yet!

Thanks again!
Duane

 

[Duane]

Well, I think I got it! Players names in col 'C' from D6 to D20, POINTS in
col'D', and so on->

First I needed to 'see' which player had the Lowest and put this value in a
cell(ie:d37):
=small(d6:d20,((countif(c6:c20,"")+1))
Then, I use this number to find my player who this number matches to in next
cell (ie: d38):
=index(c6:i20,match(d37,d6:d20,FALSE),1)

Thanks for everyone's help!
Greatly appreciated!
Duane