Hosne Ara
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.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
5 Easy Methods to Find Max Value in Range with Excel Formula
1. Excel Formula with MAX Function to Find Max Value in a 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 Value In Range in Excel (3 Methods)
2. Find Max Value Based on One Criteria 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: How to Find Lowest 3 Values in Excel (5 Easy Methods)
3. Combine Excel MAX and IF Functions to Get Max Value in a 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}
Similar Readings
- Excel Function: FIND vs SEARCH (A Comparative Analysis)
- How to Find Excel Sheet Name Using Formula (3 Examples)
- FIND Function Not Working in Excel (4 Reasons with Solutions)
- [Solved!] CTRL+F Not Working in Excel (5 Fixes)
- How to Find External Links in Conditional Formatting in Excel (2 Ways)
4. Excel MAXIFS Function to Calculate Max Value in a Range
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.
Read More: Find First Occurrence of a Value in a Range in Excel (3 Ways)
5. Find Largest Value in Range Using 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.
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.
