getting highest value

  • Thread starter Thread starter dzorug
  • Start date Start date
D

dzorug

Hi,

I have names in B1:B50, and the scores in F1:F50. I'm trying to get A
to display the name (from B column) that has the highest score (from
column). Same with the lowest score. Because the table has to remai
sorted according to date, I can't sort according to score. Can't figur
out how to do this. Any pointers will be much appreciated. Thanks muc
in advance
 
I think you should cut (or copy) the name column (B) and paste it to th
right of the score column - then do this vlookup:

=VLOOKUP(MAX(F2:F11),F1:G11,2,0)

MAX will find the greatest value in the list and the vlookup will fin
the corresponding name.

Use MIN for the lowest score find
 
thanks, but i'm trying not to move the columns around because it'
actually a much bigger, more complex table. and i'm trying t
understand the formula: how come you do the vlookup only from F
onwards and not F1? and what do the "2,0" mean? sorry, i'm still quit
new to this
 
VLOOKUP:

'Searches for a value in the left-most column of a table, and the
returns a value in the same row as specified by the user'.

=VLOOKUP(lookup_value,lookup_array,col_index_num,range_lookup).

Basically means (in order):

Lookup up the MAX score

in the array Score:Name (because score is the lookup_value it must b
in the left-most column of the lookup_array)

Where it finds a match in the score return data from the 2nd column i
the array (which is name),

and find exact matching values only (that's the 0)

Sorry, off the top of my head I can't figure a way to do it withou
shifting columns, will post again when/if I figure it out, if soneon
doesn't beat me to it
 
Instead of the VLOOKUP function you can use the MATCH
function. Hence the orginal problem can be solved with
the following function in A1

=OFFSET(B1;MATCH(MAX(F1:F50);F1:F50;0)-1;0)

Hans.
 
Back
Top