Sometimes we need to find the max value in a range using an Excel formula. Luckily Microsoft Excel has several functions which we can use to calculate the largest value from a range. This article will guide you to get maximum values in a data range using a function or a combination of functions. Besides, I will show you how to find out the location of the largest value in a range.

## 1. Applying Excel MAX Formula to Find Maximum Value in Range

Letâ€™s consider that we have a dataset containing several fruits and their sold quantities. Now from this dataset, I will find the max sold quantity using **the MAX function** in excel.

**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**. Here, we see that the highest sold quantity is**100**which is for**Watermelon**.

**Read More:** How to Find Maximum Value in Excel with Condition

## 2. Finding Max Value Based on Criterion Using Excel Formula

You can search for the highest value in a range based on one criterion using the** MAX** function. For example, in the below dataset, there are several sold quantities listed for the fruit â€˜**Apple**â€™. So, this time I will find the max sold quantity for **Apple**. Follow the below steps to get the expected output.

**Steps:**

- Type the below formula in
**Cell C17**. Then hit**Enter**.

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

- As a result, we will get the maximum sold quantity for apples, which is
**90**.

Here, **the MAX function **searches for â€˜**Apple**â€™ in the range **B5:B14**, then extracts the highest sold quantity of apples from the range **C5:C14**.

**Read More:** Find Max Value and Corresponding Cell in Excel

## 3. Combining Excel MAX and IF Functions to Get Max Value in Range

This time, I will find the max value in a range based on multiple criteria. While doing that, I am going to combine **the IF function **along with **the** **MAX** **function**. To calculate the maximum value based on multiple criteria, I have added a new column to the existing fruit dataset. The new column lists the corresponding dates for each sold quantity. Now, I will calculate the highest sold amount for â€˜**Orange**â€™ for the date: **22 March 22**.

**Steps:**

- Type the below formula in
**Cell D17**and press**Enter**.

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

- Consequently, the above formula will return the value of the maximum 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)**

Here, 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)))**

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

{**110**}

## 4. Finding Max Value in Range Using Excel MAXIFS Function

In **Excel 365**, we can find the max value in a range using **the MAXIFS function**. Using this function you can get the largest value based on both single and multiple criteria. So, using **MAXIFS** is more convenient than the combination of** MAX **&** IF** functions. Similar to the previous method, I will calculate the highest sold values of oranges for a particular date (**22 March 2022**).

**Steps:**

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

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

- As a consequence, the above formula will return the max sold value for the criteria:
**Orange**and**22 March 22**.

## 5. Getting Largest Value in Range with Excel AGGREGATE Formula

If you are using **Excel 2010** and above versions, **the AGGREGATE function** can be used to find the max value based on one or multiple criteria. However, in this method, I will calculate the largest value for the range based on one criterion. For instance, I will find the maximum sold quantity for â€˜**Apple**â€™ from the below date range (**C5:C14**).

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

Here, in the above formula, **14** indicates we are searching for the largest value in the specified range. Then 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, I have 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

You can find the position of the maximum value in a range by combining** the MATCH function **along with** the MAX function**. For example, in the below dataset **Watermelon **has the largest sold quantity (here, **100**). Now, I will find the row number where **Watermelon **is located. Letâ€™s see how to do the task.

**Steps:**

- First, type the following formula in
**Cell C17**and hit**Enter**.

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

- As a result, excel will return the row number where the max sold quantity is located. Here excel returned
**3**as the large value â€˜**100**â€™ is located in the 3rd row of the range**C5:C15**.

Here the **MAX** function returns the largest value in the range **C5:C14**. Later, the **MATCH** function returns the position of the maximum value given by the** MAX** formula.

## Things to Remember

- Other than 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**.

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

## Conclusion

In the above article, I have tried to discuss several methods to find the max value in a range using a formula in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

**Related Articles**

- How to Set a Minimum and Maximum Value in Excel
- How to Cap Percentage Values Between 0 and 100 in Excel
- Excel MIN and MAX in Same Formula

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