obtaining max value then ref

[jtwork]

i have three columns

col A = Name
col B = Value
Col C = Ref

I am trying to look up in colA for a particular name (names are repeated in alpha order) then looking to see what the max value is and then linking this to the Ref field to find out what the ref is.

i can use max(if( to find out what the max value is but then i need to link this to the ref

ive tried to put max(if in a row() function but this didnt work, anyone got any ideas?

[Loz]

Perhaps organising the list differently could make it easier?

Suggest sorting the list by column A (name) ascending, but column B (value) descending (so that the highest values come first).

Then you can use Excel's Vlookup function to search for the name in column A - it will locate the first instance for that name, which because of the sorting will be the one with the maximum value in column B, and the reference in column C can simply be returned.