**Microsoft Excel **is a powerful software. There are many default **Excel Functions** that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we need to find the maximum value based on certain conditions. We can do that by combining **the MAX** and **the IF** functions. Moreover, **the MAXIFS function** can also do the job. However, **the AGGREGATE function** can perform a similar task with some additional benefits like ignoring error values. This article will show you **3** ideal examples to use **AGGREGATE **to achieve **MAX IF** behavior in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## 3 Ideal Examples to Use AGGREGATE to Achieve MAX IF Behavior in Excel

The **AGGREGATE **function can carry out various operations depending on the **Function Number **that we’ll input in the argument. Then, we can decide on what to ignore by choosing the **Option Number**. Subsequently, we have to select the **Array **on which we’ll perform the operations. Here, we’ll show how you can use this function to achieve the **MAX IF **behavior. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains **Region**, **Product**, **Net Sales**, & **Invoice Date**. This article will show the process to extract the maximum sales of a product from a particular region.

### 1. Use AGGREGATE Function for MAX IF to Find Last Date in Excel

In our first example, we’ll determine the last invoice date for the product **TV**. The last date will have the maximum value in terms of numbers. In this regard, we’ll input **14 **as the function number. **14 **is for the **LARGE **function. If you select function number **4 **which is **MAX**, you’ll get an error. Because it can’t calculate based on conditions. Therefore, follow the steps below to perform the task.

**STEPS:**

- First, select cell
**D12**. - Then, type the formula:

`=AGGREGATE(`

- As a result, you’ll see the function numbers along with the function names.
- Double-click the
**LARGE function.**

- Consequently, choose the option number which suits your requirement.
- In this example, we choose
**6**(**Ignore error values**).

- Now, type the array:

`(E5:E10)/(C5:C10=C5)`

- Finally, the formula is:

`=AGGREGATE(14,6,(E5:E10)/(C5:C10=C5),1)`

- Next, press
**Enter**to get the output. - Thus, you’ll get the last invoice date for
**TV**.

**NOTE:**In the array section,

**E5:E10**is the

**Net Sales**range. And

**C5:C10=C5**looks for the product

**TV**.

**1**is for the

**1st**largest number.

### 2. Determine Maximum Based on Criteria with Excel AGGREGATE Function

Now, we’ll show how you can get the maximum **Net Sales** of **TV**. However, instead of using cell range in the array argument, we’ll input table reference. You can easily create a table by pressing the **Ctrl **and **T** keys together after selecting the desired range. In this example, the condition is **TV**. So, learn the following steps to determine maximum sales based on single criteria with the **Excel AGGREGATE** function. We’ll use **14 **as the function number and** 6 **as the option number for this example also.

**STEPS:**

- Firstly, we create a table (
**Table1**) by selecting the range**B4:E10**. - See the picture below where we have the table.

- After that, input the formula:

`=AGGREGATE(14,6,Table1[`

- Hence, the table headers will pop out.
- Choose
**Net Sales**.

- Afterward, type
**/Table1[**and table headers will appear again. - There, select
**Product**.

- Lastly, the whole formula is:

`=AGGREGATE(14,6,Table1[Net Sales]/(Table1[Product]="TV"),1)`

- Next, press
**Enter**. - Consequently, you’ll get an accurate result.

**NOTE: Table1[Product]=”TV”**looks for

**TV**in the

**Product**range. And

**1**is for the

**1st**maximum value.

### 3. Insert AGGREGATE to Achieve Maximum Based on Double Criteria

However, we may face real-life problems where we have to find out the maximum value from a range based on multiple conditions. In our last example, we’ll demonstrate how you can do that with the **AGGREGATE **function. The conditions are **West **and **TV **as the **Region** and the **Product **respectively. Hence, follow the process below.

**STEPS:**

- First of all, click cell
**D12**. - Then, type the formula:

`=AGGREGATE(14,6,(B5:B10=B5)`

- Here,
**B5:B10=B5**matches the first condition which is**West**as**Region**.

- Next, type
***(C5:C10=C5)**, and the formula becomes:

`=AGGREGATE(14,6,(B5:B10=B5)*(C5:C10=C5)`

- Subsequently, this part looks for the product
**TV**.

- Finally, put
***D5:D10,1**and the complete formula is:

`=AGGREGATE(14,6,(B5:B10=B5)*(C5:C10=C5)*D5:D10,1)`

- Press
**Enter**. - In this way, it’ll return the maximum value based on double criteria.

**NOTE: D5:D10**is the

**Net Sales**range from which we’ll extract the maximum value.

**1**is for the

**1st**largest value.

## Conclusion

Henceforth, you will be able to use **AGGREGATE **to achieve **MAX IF **behavior in **Excel **following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.