If you want to get the highest value from a range of values using a particular criterion, then the combination of MAX and IF function can be used to filter the array of values, while returning the max value.
How to use Max if criteria match
In order to get the best out of the formula, it is necessary for the data to be purposefully listed, and the arguments for the formula to be properly understood:
Bearing that in mind, let us understand the formula argument –
- Criteria_range: This represents the array list. It is a range of cell values and hence will look like this B2:B500. This range of heterogeneous values contains other subsets of homogeneous values. The criteria range is the filter boundary.
- Criteria: This represents the actual value that forms the basis for the filtering process.
- Value_range: This represents the array list of values upon which the filtering is done to determine the maximum.
Let us assume for a moment that data is collected after studying the distance covered by a man on the first 10 days of January and February. To find the maximum distance covered in January, we do the following:
- Set the values
Figure 1 – Data table
- Prepare the criteria and result table
Figure 2 – Showing result table for criteria value and longest distance
- Enter the criteria
Figure 3 – Criteria entered as January
- Input the IF function formula
Figure 4 – Showing the formula to get the longest distance walked
- View Result
Figure 5 – Showing result of ‘max if criteria match’