Identify row number from cell with max value

[weeping fig]

Hi new to Excel but enjoying it.

I have a column of numbers, say in column "A" that increase to a maximum and then starts to decrease.

I have Identified the cell that has the maximum number in the column using the Max function and this number is then updated in another part of my spreadsheet but I need to identify what row number this is.

What formula would show this?

Many thanks

 

[VBA Geek]

if your data goes from A1 to A100 then use below to find row number

MATCH(MAX($A$1:$A$100);$A$1:$A$100;0)

 

[weeping fig]

Hi, thanks for your quick response and help with this.

I tried the formula but an error was returned. Not sure if I did right but I changed your 2 semi colons to commas and a row number was returned but was incorrect.
The cell range that I am using is AL14 to AL148.
This time row 18 was returned and should have been row 31.

Many thanks

 

[Rick Rothstein]

Hi, thanks for your quick response and help with this.

I tried the formula but an error was returned. Not sure if I did right but I changed your 2 semi colons to commas and a row number was returned but was incorrect.
The cell range that I am using is AL14 to AL148.
This time row 18 was returned and should have been row 31.

See if this variation on VBA Geek's formula works for you...

=MATCH(MAX($AL$14:$AL$148),$AL$14:$AL$148,0)+8

 

[VBA Geek]

ADVERTISEMENT

in your match function you may also input the whole range AL1:AL148 as second argument, then as first argument the max AL14:AL148, then exact match as third.

 

[Marcelo Branco]

Try

=MATCH(MAX($AL$14:$AL$148),$AL$14:$AL$148,0)+ROW($AL$14)-1

M.

 

[weeping fig]

ADVERTISEMENT

Hi great, not 8 but add 13. Do you know why I need to add 13 to the formula?

Thanks for your help, first thread and quick success.

 

[Rick Rothstein]

Hi great, not 8 but add 13. Do you know why I need to add 13 to the formula?

Yes, 13, not 8... I forgot to change it from the example range I was testing with. Glad you were able to figure it out on your own. The number being added is one less than the starting row number for the range because MATCH returns a count starting from the beginning of the range given it, not Row 1, so we have to add the offset from Row 1 to the beginning of the range to account for that.

 

[Marcelo Branco]

Hi great, not 8 but add 13. Do you know why I need to add 13 to the formula?

Thanks for your help, first thread and quick success.

See my formula in #6

About your question:
Because MATCH returns the relative position of the MAX inside the range AL14:AL148.

So, 1 (the first position) corresponds to AL14; 2 corresponds to AL15; 3 corresponds to AL16 and so on.

Therefore to get the row number of the MAX value, you need to add the row number of the first cell (AL14) and subtract 1.

M.

 

[Aladin Akyurek]

What if you have multiple instances of the max value in $AL$14:$AL$148?