Formulas to Find Maximum Value with Multiple Criteria in Excel!
Tags: excelformula, formula
Published: 2023-04-05 10:18:10

---
title: Formulas to Find Maximum Value with Multiple Criteria in Excel!
published: true
date: 2023-04-04 11:30:48 UTC
tags: ExcelFormula,Formula
canonical_url: https://aawexcel.com/excel-formulas-maximum-value-with-multiple-criteria/
---

The maximum value is the **largest number of a set of values**. Sometimes, we want to get the largest value from the list, it can be a score or price, etc. For that, you can use a simple formula in Excel. In this tutorial, we will see how to **get the**  **maximum value** from a set of data **based on**  **multiple criteria in Excel**. Get an official version of ** MS Excel**  from the following link: 

[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

![Find largest number in a group](https://aawexcel.com/wp-content/uploads/2023/04/Find-largest-number-in-a-group.webp)
_Find the largest number in a group_

## General Formula

Here we come up with a simple formula to find the maximum value in a range with multiple criteria in the worksheet.

**=[MAX](https://aawexcel.com/how-to-use-excel-max-function/)([IF](https://aawexcel.com/how-do-we-use-excel-if-function/)(range1=criteria1,[IF](https://aawexcel.com/how-do-we-use-excel-if-function/)(range2=criteria2,values)))**

## Syntax Explanations

- **MAX** – The **[MAX Funct](https://aawexcel.com/how-to-use-excel-max-function/)[ion](https://aawexcel.com/how-to-use-excel-max-function/)** helps to return the largest numeric value from the range of input values.
- **IF** – This function will return one value for a TRUE result, and another for a FALSE result. Read more on the **[IF Function](https://aawexcel.com/how-do-we-use-excel-if-function/)**.
- **Values** – It represents the input values from the given worksheets.
- **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.
- **Criteria** – It is the specific month that wants to find the maximum value.
- **Range** – It represents the input ranges.

## How to Apply Formula to Get Maximum Value?

In order to extract the maximum value from the range of input values from Excel, we can use an Excel formula. This formula can make use of the [IF function](https://aawexcel.com/how-do-we-use-excel-if-function/) and [MAX Function](https://aawexcel.com/how-to-use-excel-max-function/) of Excel. As we said earlier, the IF function returns the result based on the given condition. Whereas, the MAX function returns the largest value in the selected array.

For instance, we have given a **list of items that are sold in the specific three months** for the last three years in a stationery shop. We need to find the specific item that sold for the highest price from the list. For that, we can apply a formula to find out the result.

![Product details](https://aawexcel.com/wp-content/uploads/2023/04/Product-details.webp)
_Product details_

Use the below-mentioned formula in any cell where you want to get the output result.

**=MAX(IF(B3:B11=G2,IF(C3:C11=G4,D3:D11)))**

![Enter the formula](https://aawexcel.com/wp-content/uploads/2023/04/Enter-the-formula-1.webp)
_Enter the formula_

Once you have entered the formula,  **click ** on the  **Enter ** button to execute it and get the result. You will get the maximum price value for the specific item.

![Output](https://aawexcel.com/wp-content/uploads/2023/04/Output-1.webp)
_Output_

If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the following section.

## Alternate Example

Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.

- In the below screenshot, we have given the **student’s mark details**. We want to get the highest score of a particular student in a specific subject. We need to consider all the tests that are conducted in the last six months.

![Student scores](https://aawexcel.com/wp-content/uploads/2023/04/Student-scores.webp)
_Student scores_

- Apply the below-given formula to get the result on your spreadsheet.

**=MAX(IF(C3:C11=H2,IF(D3:D11=H3,E3:E11)))**

![Type the formula](https://aawexcel.com/wp-content/uploads/2023/04/Type-the-formula-1.webp)
_Type the formula_

- You have to  **press ** the  **Enter ** button to get the result.

![Result](https://aawexcel.com/wp-content/uploads/2023/04/Result-1.webp)
_Result_

## Conclusion

From this guide, you can easily learn the simple formulas used for finding the **maximum value based on multiple criteria in Excel**. Hope you like this article. Don’t forget to share your worthwhile feedback. For more updates keep in touch with our website **[AawExcel](https://aawexcel.com/)** and [**Excel Formulas**](https://aawexcel.com/excel-formula/) **!**

## Video Tutorial

The below-given video will show how to apply an Excel formula to find the maximum value based on multiple criteria.

<video controls poster="https://aawexcel.com/wp-content/uploads/2023/04/Find-Maximum-Value-with-Multiple-Criteria.png.webp" src="https://res.cloudinary.com/practicaldev/video/fetch/https://aawexcel.com/wp-content/uploads/2023/04/Find-Largest-Value.webm"></video>

**Read More:**

- [Formulas to Find Maximum Value with Multiple Criteria in Excel!](https://aawexcel.com/excel-formulas-maximum-value-with-multiple-criteria/)
- [CTRL + S: Shortcut to Save Workbook in Excel (Windows & Mac)](https://aawexcel.com/shortcut-to-save-workbook-in-excel/)
- [How to Extract Email Address from Text String in Excel?](https://aawexcel.com/how-to-extract-email-address-from-text-string-in-excel/)
- [How to Filter Cells By Font Color in Excel?](https://aawexcel.com/how-to-filter-cells-by-font-color-in-excel/)
- [Formulas to Rank Items with One or more Criteria in Excel!](https://aawexcel.com/excel-formulas-to-rank-items-with-one-or-more-criteria/)