Max Value Problem

R

REW2705

I want to search a column of data (D2:D86) 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
 
D

Duke Carey

try

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

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

Duke
 
A

AlfD

Hi!

Try again:

=INDEX(C2:C85,MATCH(MAX(D2:D85),D2:D85,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
 
R

Ron Rosenfeld

I want to search a column of data (D2:D86) 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
Click to expand...


=INDEX(C2:C1000,MATCH(MAX(D2:D1000),D2:D1000,0))

Adjust your ranges to take into account the largest it might grow to.

Another method to handle ranges that might grow is to use dynamic ranges, or
dynamic named ranges. These use the OFFSET and COUNT functions to "size" the
range. If that is of interest, see http://www.cpearson.com/excel/named.htm

An example of a formula adapted to your ranges might be:

=INDEX(OFFSET(C2,0,0,COUNTA(C2:C65535)),
MATCH(MAX(OFFSET(D2,0,0,COUNTA(D2:D65535))),
OFFSET(D2,0,0,COUNTA(D2:D65535)),0))




--ron
 
Top