getting highest value

[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

 

[Kiaat]

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

 

[dzorug]

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

 

[JE McGimpsey]

One way:

=INDEX(B1:B50,MATCH(MAX(F1:F50),F1:F50,FALSE))

 

[Kiaat]

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

 

[dzorug]

JE McGimpsey, you're a genius. It works

 

[Hans D. Jensen]

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.

 

[dzorug]

thanks folks. what if i've got two highest numbers