How to Find Max Value in Range with Excel Formula: 5 Easy Methods

Method 1 – Applying Excel MAX Formula to Find Maximum Value in Range

Steps:

Type the below formula in Cell B15 and press Enter from the keyboard.

=MAX(C5:C12)

Upon entering the formula, we will get the largest value in the data range C5:C12. See that the highest sold quantity is 100 which is for Watermelon.

Method 2 – Finding Max Value Based on Criterion Using Excel Formula

Steps:

Type the below formula in Cell C17. Hit Enter.

=MAX((B5:B14=B17)*(C5:C14))

Get the maximum sold quantity for apples, which is 90.

The MAX function searches for ‘Apple’ in the range B5:B14, then extracts the highest sold quantity of apples from the range C5:C14.

Method 3 – Combining Excel MAX and IF Functions to Get Max Value in Range

Steps:

Type the below formula in Cell D17 and press Enter.

=MAX(IF(B5:B14=B17,IF(C5:C14=C17,D5:D14)))

The above formula will return the value of the maximum number of sold oranges for 22 March 22.

How Does the Formula Work?

B5:B14=B17

The above part of the formula checks whether the value of Cell B17 is present in the range B5:B14 and returns:

{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}

IF(C5:C14=C17,D5:D14)

The IF function finds the date of Cell C17 in the range C5:C17 and returns the sold fruit quantities if the dates are matched.

{FALSE;70;FALSE;FALSE;110;FALSE;FALSE;100;FALSE;60}

MAX(IF(B5:B14=B17,IF(C5:C14=C17,D5:D14)))

The MAX IF formula returns the maximum number of oranges for 22 March 2022, which is:

{110}

Method 4 – Finding Max Value in Range Using Excel MAXIFS Function

Steps:

Type the following formula in Cell D17. Next, hit Enter.

=MAXIFS(D5:D14,B5:B14,B17,C5:C14,C17)

The above formula will return the max sold value for the criteria: Orange and 22 March 22.

Method 5 – Getting Largest Value in Range with Excel AGGREGATE Formula

Steps:

Type the below formula in Cell C17 and press Enter from the keyboard.

=AGGREGATE(14,4,(B5:B14=B17)*C5:C14,1)

Upon entering the formula, you will get the highest sold quantity for Apple from the range C5:C14.

In the above formula, 14 indicates we are searching for the largest value in the specified range. Choosing 4 in the formula means we are ignoring nothing (error values, hidden rows, and so on) while calculating. At the end of the AGGREGATE formula, we entered k = 1, because I am looking for the 1st largest sold quantity for ‘Apple’.

Find Location of Max Value in a Range with Excel Formula

Steps:

Type the following formula in Cell C17 and hit Enter.

=MATCH(MAX(C5:C14),C5:C14,0)

Excel will return the row number where the max sold quantity is located. Excel returned 3 as the large value ‘100’ is located in the 3rd row of the range C5:C15.

The MAX function returns the largest value in the range C5:C14. The MATCH function returns the position of the maximum value given by the MAX formula.

Things to Remember

Using excel functions, you can find the max value from a range from Excel Ribbon. To do that, follow the path: Home > Editing group > AutoSum > Max. Then press Enter.