Max Value Problem
 

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

Hi!

Try =INDIRECT(ADDRESS(MAX(D2:D86),3))

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

Alf

Hi!

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

Alf

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

"REW2705" wrote:

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

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

On Thu, 31 Mar 2005 05:41:01 -0800, "REW2705"
wrote:

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


=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