# How to find the top seller in each region in Excel

Find each region's highest sales with MAXIFS and look up the rep with that region and amount: `=XLOOKUP(1,($B$2:$B$10=E2)*($C$2:$C$10=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)),$A$2:$A$10)`.

**Excel:** `=XLOOKUP(1,($B$2:$B$10=E2)*($C$2:$C$10=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)),$A$2:$A$10)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Rep | Region | Sales |  | Region | Top rep |
| Ana | East | 48200 |  | East |  |
| Ben | West | 61750 |  | West |  |
| Cara | East | 57300 |  | North |  |
| Dev | North | 39800 |  | South |  |
| Eli | West | 52400 |  |  |  |
| Fay | South | 44150 |  |  |  |
| Gus | North | 47600 |  |  |  |
| Hana | South | 51900 |  |  |  |
| Ivo | East | 53800 |  |  |  |

Formula in F2, filled to F5: `=XLOOKUP(1,($B$2:$B$10=E2)*($C$2:$C$10=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)),$A$2:$A$10)`

Result: Cara; Ben; Gus; Hana

## How it works

`MAXIFS($C$2:$C$10,$B$2:$B$10,E2)` is the best sales figure in the region: $57,300 for East. XLOOKUP then finds the row where the region is East and the sales equal that figure, and returns the rep. Checking the region as well matters, because the same figure could appear in another region.

## Other ways

- `=INDEX($A$2:$A$10,MATCH(1,($B$2:$B$10=E2)*($C$2:$C$10=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)),0))`: For Excel 2019 (Ctrl+Shift+Enter). MAXIFS needs Excel 2019 or later.

## Common mistakes

- Looking up only the amount, `XLOOKUP(MAXIFS(…),$C$2:$C$10,$A$2:$A$10)`, can return a rep from another region who sold the same amount.
- If two reps in a region tie, the one higher in the list is returned.

## Questions

**How do I return the top sales figure too?**

Next to it, use `=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)`.

**How do I find the lowest seller in each region?**

Swap MAXIFS for MINIFS.

**How do I build the region list automatically?**

Use `=UNIQUE(B2:B10)` in E2 and point the formula at `E2#`.

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/top-seller-in-each-region