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

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.

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

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

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

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

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

• 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