When working with Excel, you often need to calculate the maximum value in a row or in a column by using the MAX function. The following formula is to figure out the row number for the max value.
Example: In the following dataset, groups are in column A, regions are in column B, and salaries are in column C.
Formula: To count the row number for the maximum value.
=MATCH(MAX(C1:C14), C1:C14, 0)
- MAX(C1:C14) is to figure out the max data in the data range;
- 0 is the match type, meaning to find the exact value.
The maximum value in the example returns $100,835,00, and it is on the fourth row. Please remember the row number will be relative if your data range did not start from the first row.
For example, if you only include the data range C4:C14 in the formula, the result will return 1, which the relative position in the specified range.
=MATCH(MAX(C4:C14), C4:C14, 0)
