# How to return the entire row of the max value in Excel

Filter for the rows that equal the maximum: `=FILTER(A2:D10,D2:D10=MAX(D2:D10))`. Ties all come back.

**Excel:** `=FILTER(A2:D10,D2:D10=MAX(D2:D10))` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Rep | Region | Deals | Revenue |
| Ana Ruiz | East | 12 | 48200 |
| Raj Patel | West | 9 | 51750 |
| Lee Chen | East | 15 | 39900 |
| Kim Ortiz | North | 7 | 62300 |
| Mo Diaz | West | 11 | 45100 |
| Sam Cole | South | 8 | 30500 |
| Eve Lund | North | 14 | 57800 |
| Noor Ali | South | 10 | 44600 |
| Ivy Shah | East | 13 | 52900 |

Formula in A13: `=FILTER(A2:D10,D2:D10=MAX(D2:D10))`

Result (spills 1 × 4): Kim Ortiz, North, 7, $62,300

## How it works

MAX finds the highest revenue, 62,300. Comparing every revenue with it is TRUE only on that rep's row, and FILTER returns the full row. If two reps tie for the top, both rows come back, which a lookup would hide.

## Other ways

- `=XLOOKUP(MAX(D2:D10),D2:D10,A2:D10)`: XLOOKUP returns the first row with the maximum. Same result unless there's a tie.
- `=TAKE(SORT(A2:D10,4,-1),1)`: Sort by revenue and take the top row. Change 1 to 3 for the top three.

## Common mistakes

- INDEX/MATCH and XLOOKUP stop at the first match, so a tie for the top silently loses a row.
- Point MAX at the same column you compare: `D2:D10=MAX(D2:D10)`. Mixing up columns returns #CALC! because nothing matches.

## Questions

**How do I return the row of the minimum value?**

Use MIN: `=FILTER(A2:D10,D2:D10=MIN(D2:D10))`.

**How do I return the row with the highest value in one region?**

Use MAXIFS and a second test: `=FILTER(A2:D10,(B2:B10="East")*(D2:D10=MAXIFS(D2:D10,B2:B10,"East")))`.

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/return-the-entire-row-of-the-max-value