Exceljet

Quick, clean, and to the point

Position of max value in list

Excel formula: Position of max value in list
Generic formula 
=MATCH(MAX(range),range,0)
Explanation 

To get the position of the maximum value in a range (i.e. a list, table, or row), you can use the MAX function together with the MATCH function.

In the example shown, the formula in I5 is:

=MATCH(MAX(C3:C11),C3:C11,0)

Which returns the number 4, representing the position in this list of the the most expensive property.

How this formula works

The MAX function first extracts the maximum value from the range C3:C11.

In this case, that value is 849900.

This number is then supplied to the MATCH function as the lookup value. The lookup_array is the same range C3:C11, and the match_type is set to "exact" with 0.

With those arguments, MATCH locates finds the maximum value inside the range and returns the relative position of the value in that range.

In this case, the position corresponds to a relative row number, but in a horizontal range, the position would correspond to a relative column number.

Note: in case of duplicates (i.e. two or more max values that are the same) this formula will return the position of the first match, the default behavior of the MATCH function.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables