# Identify row number from cell with max value

#### weeping fig

##### New Member
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### VBA Geek

##### MrExcel MVP
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)

Last edited:

#### weeping fig

##### New Member
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### MrExcel MVP
Try

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

M.

#### weeping fig

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

#### Rick Rothstein

##### MrExcel MVP
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.

Last edited:

#### Marcelo Branco

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

See my formula in #6

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.

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

Replies
1
Views
122
Replies
9
Views
117
Replies
9
Views
119
Replies
3
Views
174
Replies
36
Views
684

1,195,655
Messages
6,010,939
Members
441,577
Latest member
Alonshow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back