Locate cell position for max value in column

**maladroit1** · 12-07-2007, 05:07 PM

I'm sure there is some fairly simple way to search for the maximum value in a column of numbers and return the cell position of that maximum value, but I haven't been able to figure this out. Help, please!!??

**Ron Coderre** · 12-07-2007, 05:17 PM

Not quite sure about how you want to apply it....

so...
Maybe something like:

This formula returns the Row Number of the cell containing the first instance of the max column value:

=MATCH(MAX(A:A),A:A,0)

Does that help?
Is that something you can work with?

**Aladin Akyurek** · 12-08-2007, 06:13 PM

Originally Posted by maladroit1

I'm sure there is some fairly simple way to search for the maximum value in a column of numbers and return the cell position of that maximum value, but I haven't been able to figure this out. Help, please!!??

Lets assume the following sample in A2:A10...

6
8
7
9
8
10
3
10
9

In D2 enter:

=MAX(A2:A10)

In D3 enter:

=COUNTIF(A2:A10,D2)

In D4 enter: Result

In D5 enter:

=IF(ROWS($D$5:D5)<=$D$3,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($D$5:D5)),"")

which must be confirmed with control+shift+enter then copied down.

This formula yields the relative positions of the instances of the max value.

The following...

=IF(ROWS($D$5:D5)<=$D$3,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)),ROWS($D$5:D5)),"")

yields the native row positions of the instances of the max value.

And...

=IF(ROWS($D$5:D5)<=$D$3,CELL("address",INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($D$5:D5)))),"")

lists the actual cells they occupy.

**Ron Coderre** · 12-08-2007, 06:42 PM

I like your idea to list each relative position of the max value.

I'd prefer to avoid the array formulas, though, and go with something like this:

Using your sample structure:
D2: =MAX(A2:A51)
D3: =COUNTIF(A2:A51,D2)

D4: Result
D5: =IF(ROWS($5:5)<=$D$3,MATCH($D$2,$A$2:$A$51,0),"")

D6: =IF(ROWS($5:6)<=$D$3,MATCH($D$2,INDEX($A$2:$A$51,D5+1):$A$51,0)+D5,"")

Copy D6 down as far as needed.

Note: Those formulas return the RELATIVE POSTITION of the 
max value(s) in the referenced range.  If the actual row number is required:
D5: =IF(ROWS($5:5)<=$D$3,MATCH($D$2,$A$2:$A$51,0)+ROW($A$1),"")

EDITED TO CORRECT THE BELOW FORMULA
D6: =IF(ROWS($5:6)<=$D$3,MATCH($D$2,INDEX($A:$A,D5+1):$A$51,0)+D5,"")