Max Value Problem

[Guest]

I want to search a column of data (D286) and have the search show not that
value but the text in the adjacent C cell. The only what that i can think to
do this is have a if loop that searches each cell to see if it is the max, if
it is it shows the adjacent cell text, if not then it checks the next cell,
but the column has 85 values and will grow, i dont want a cell that has a
function that long. Any suggestions?????


Please Help

Ryan

 

[AlfD]

Hi!

Try =INDIRECT(ADDRESS(MAX(D286),3))

The final 3 refers to column 3 (i.e. C)

Alf

 

[AlfD]

Hi!

Scrub that: it works sometimes.
Back to the drawing board

Alf

 

[Guest]

try

=VLOOKUP(MAX(D286),D2:C86,2,FALSE)

If there are multiples of the max value, this will get only the first
occurrence

Duke

 

[AlfD]

Hi!

Try again:

=INDEX(C2:C85,MATCH(MAX(D285),D285,0))

The problem with lookup is that the array is back-to-front. If it were
numbers in col C and text in D, no problem.

Alf