Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Hi!
Try =INDIRECT(ADDRESS(MAX(D2:D86),3)) The final 3 refers to column 3 (i.e. C) Alf |
#3
|
|||
|
|||
Hi!
Scrub that: it works sometimes. Back to the drawing board Alf |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL 2003 PROBLEM | Excel Worksheet Functions | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) |