How to return the entire row of the max value in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:D10,D2:D10=MAX(D2:D10))Filter for the rows that equal the maximum: =FILTER(A2:D10,D2:D10=MAX(D2:D10)). Ties all come back.
- Result on the example
- a 1-row list that spills from A13 in
A13 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in A13. Change any input and the result updates, or edit the formula to experiment.
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.
=FILTER(A2:D10,D2:D10=MAX(D2:D10))Result{1 row × 4 cols} "Kim Ortiz", "North", 7, 62300
A2:D109 rows × 4 cols: "Ana Ruiz", "East", 12, $48,200, …A range: 9 rows × 4 columns.
D2:D109 rows × 1 col: $48,200, $51,750, $39,900, $62,300, …A range: 9 cells in one column.
MAX(D2:D10)62300MAX: Largest number.- number1
D2:D109 rows × 1 col: $48,200, $51,750, $39,900, $62,300, …
- number1
D2:D10=MAX(D2:D10){9 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(A2:D10,D2:D10=MAX(D2:D10)){1 row × 4 cols} "Kim Ortiz", "North", 7, 62300FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:D109 rows × 4 cols: "Ana Ruiz", "East", 12, $48,200, … - condition1
D2:D10=MAX(D2:D10){9 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:D10,D2:D10=MAX(D2:D10)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 1 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(MAX(D2:D10),D2:D10,A2:D10) | spills 1 × 4 | XLOOKUP returns the first row with the maximum. Same result unless there's a tie. |
| =TAKE(SORT(A2:D10,4,-1),1)Excel only | spills 1 × 4 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return the entire row of the max value
A13, return the whole row (all four columns) for the rep with the highest revenue.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"))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.