Google Sheets – Get Row Number of MAX(Range) Value

google sheets

I have a spreadsheet with several items, their volumes and margins. I have no problem in finding the maximum margin with =MAX(Range). What I need to find is the row number of that value.

Let's say I have these values:

   A      B    C
1 Item1   5m³  70%
2 Item2   10m³ 80%
3 Item3   2m³  50%

The maximum margin would be 80%. Now i need to know that this value belongs to Item 2 in Row 2.
Is there a way to find that out?

Best Answer

Use the following formula to do that.

Formula

=MATCH(MAX(E2:E4), E2:E4, 0)

If you want to include the header, then you have to use this formula:

=MATCH(MAX(E2:E4), E1:E4, 0)

Screenshot

enter image description here

Remark

The last criterium in the MATCH formula, 0 will trigger the function to find the first entry. Use 1 to show the last entry.

Example

I've created an example file for you: Get Row-Number of a MAX(Range) Value?

Related Topic