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.

**Formula**

`=MAX(IF(criteria_range=criteria,value_range))`

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

**Example**

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

## Leave a Comment