How To Find The Position Of The Maximum

2017-08-28

Example

=MATCH(MAX(B3:B10),B3:B10,0)

Generic Formula

=MATCH(MAX(Range),Range,0)

Range – This the range of values in which you would like to find the relative position of the maximum value.

What It Does

This formula will return the relative position of the maximum value in a given range of numerical values. The formula will work for any one dimensional vertical or horizontal range. If the range contains multiple maximums, then the formula will return the position of the top most or left most maximum.

How It Works

This formula uses the MAX function to first find the value of the maximum within the given range, then it uses the MATCH function to find the relative position of this maximum within the range.

In our example MAX(Range) returns the value 9 since 9 is the maximum value in the range of values {1;0;8;6;9;7;9;6}.

MATCH(9,Range,0) looks for a 9 in the Range and will return the position of the first 9 it finds. The 0 in the formula is a predefined Excel parameter that tells the MATCH function to find an exact match in the Range. In our example MATCH(9,{1;0;8;6;9;7;9;6},0) returns the value 5 since the first 9 in {1;0;8;6;9;7;9;6} is in the 5th position.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest