When working with long ranges of data we need to find the max value among the range If the given statement stands True. In this article, we will learn about how to calculate the Maximum value if the criteria match in Excel. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Generic Formula:
- To get the maximum value based on certain criteria, you can use the below formula.
=MAX(IF(criteria_range=criteria,value_range))
Syntax Explanations:
- MAX – In Excel, the MAX function will return the largest numeric value from the range of input values.
- IF – This function will help to return one value for a TRUE result, and another for a FALSE result. Read more on the IF function.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Value_range – It represents the input data given in the worksheet.
- Criteria_range – It is one of the inputs that contain the criteria range.
- Criteria – It represents the criteria.
Practical Example:
Now we are going to see how to find the maximum value in Excel. Refer to the below image.
- Here, we will give the input values in Column B and Column C.
- Then, apply the given formula in the formula bar section based on your input ranges. You can also see the formula in the selected cell.
- Press CTRL + SHIFT + ENTER Keys to get the exact matching value.
- Finally, we will get the result in the selected cell.
Wrap-Up:
Hope you understood the simple formulas used to find the maximum value if the criteria match in Excel. Make use of it. Found something that we’ve not covered? Stuck on something? Let me know in the below comment section. Click here to know more about Geek Excel!! *and Excel Formulas *!!
Top comments (0)