To find the maximum value among duplicates in Excel, the VLOOKUP function alone is not sufficient. Let’s look at the MAXIFS function for calculating the maximum value in Excel and the MINIFS function for calculating the minimum value.
Find the Maximum Value Among Duplicates in Excel
There are several ways to find a maximum value. Today, we’ll look at how to find the maximum value using the MAXIFS function.
MAXIFS Function Definition
This function returns the largest value in a range that meets specified criteria. Available starting with Excel 2016, it combines the functionality of the MAX function and the IF function.
MAXIFS Function Syntax
The Excel MAXIFS function has the following syntax.
=MAXIFS(max_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
- max_range: The range in which to find the maximum value.
- criteria_range1: The range to evaluate for criteria1.
- criteria1: The first criterion to evaluate.
- criteria_range2: The range to evaluate for criteria2.
- criteria2: The second criterion to evaluate.
Starting with the second criterion, criteria are optional.
Find the Maximum Value Using the MAXIFS Function
To find the maximum value among duplicates in Excel, use the MAXIFS function as shown below.
First, to find the maximum value in a single column, use the MAX function. The formula used is shown below.
G3 cell =MAX(E3:E13)
The formula returns 1,700, the maximum Sales Quantity value in the data below.
To find the maximum value among duplicates in Excel, use the MAXIFS function with the following formula.
G6 cell =MAXIFS(E3:E13,B3:B13,G7)
It finds and returns the highest Sales Quantity for John among the duplicate entries in the Seller name column.
Conclusion
MAXIFS is a function for finding duplicate values in Excel and then finding the maximum value among them. However, Excel also includes many other functions. For example, the SUM function calculates the sum of numbers in a range, while the AVERAGE function calculates an average. The IF function returns a value based on a condition, and the VLOOKUP function looks up a specific value and returns another value. Using these functions makes it easier to handle more complex calculations. Learning how to use functions is important for Excel users because they can improve work efficiency.