# How to find the highest value for each category in Excel

List the categories with UNIQUE and get each maximum with MAXIFS: `=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))`.

**Excel:** `=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=QUERY(A1:C13,"select A, max(C) group by A label max(C) 'Top sale'",1)`

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Region | Rep | Sale |
| East | Ana | 4200 |
| West | Raj | 3150 |
| East | Lee | 1900 |
| North | Kim | 2620 |
| West | Mo | 3880 |
| East | Sam | 2440 |
| North | Eve | 1310 |
| West | Noor | 2075 |
| North | Ivy | 2960 |
| East | Tom | 1150 |
| West | Raj | 990 |
| North | Kim | 1405 |

Formula in E2: `=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))`

Result (spills 3 × 2): East, $4,200; West, $3,880; North, $2,960

## How it works

UNIQUE returns East, West and North. Given that list as its criteria, MAXIFS returns the largest sale for each one, and HSTACK sets the two columns side by side. The same pattern works with MINIFS for the smallest value or AVERAGEIFS for the average.

In Google Sheets, QUERY's group by with max() is the usual way to do this.

## Other ways

- `=LET(r,UNIQUE(A2:A13),HSTACK(r,MAXIFS(C2:C13,A2:A13,r),MINIFS(C2:C13,A2:A13,r)))`: Add more statistics as extra columns. (A third column with each region's smallest sale.)

## Common mistakes

- MAX on its own ignores the category: `MAX(C2:C13)` is the largest sale overall.
- MAXIFS needs Excel 2019 or later. In older versions, use `MAX(IF(A2:A13="East",C2:C13))` as an array formula.

## Questions

**How do I show who made the top sale in each region?**

Return the matching rows instead: `=FILTER(A2:C13,C2:C13=MAXIFS(C2:C13,A2:A13,A2:A13))`.

**How do I get the lowest value per category?**

Use MINIFS in the same place: `MINIFS(C2:C13,A2:A13,UNIQUE(A2:A13))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/highest-value-for-each-category