# 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.

## Related Articles

<< Go Back to Excel MAX Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF