The MAX function in Excel

Q: How do I find the name that goes with the highest value?

Look up the MAX: =XLOOKUP(MAX(C2:C6),C2:C6,A2:A6). In older Excel use =INDEX(A2:A6,MATCH(MAX(C2:C6),C2:C6,0)).

Q: How do I stop a formula going below zero?

Wrap it in MAX with 0: =MAX(0,C2-B2). Whenever the calculation is negative, 0 is larger, so 0 is returned.

MAX returns the largest number in a set of cells: the biggest order, the longest delay, the latest date. It skips empty cells, text and TRUE/FALSE in ranges. Pair it with XLOOKUP or INDEX/MATCH to find who or what the top value belongs to, a very common interview question.

Syntax

=MAX(number1, [number2], ...)

number1	The first number, cell or range to check, like `C2:C6`.
number2, ...	Optional. More numbers or ranges. MAX returns the largest value across all of them.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

In E2, use MAX to find the longest delivery time, in minutes, from C2:C8.

	A	B	C	E
1	Driver	Route	Minutes	Longest
2	Sam	North loop	42
3	Priya	Old town	57
4	Leo	Riverside	38
5	Mia	Airport	71
6	Jonas	Harbour	49
7	Aisha	Hillside	63
8	Tomás	Market	35
9
10
11

Tip: Give MAX the Minutes column, from the first driver to the last.

Examples

=MAX(C2:C6)→ 61200

The highest actual sales figure on the team.

=XLOOKUP(MAX(C2:C6),C2:C6,A2:A6)→ "Ana"

Finds the top figure, then looks it up to return the rep's name.

=MAX(0,C3-B3)→ 0

Ben's amount over target, but never below 0. He missed target, so the result is 0 instead of a negative number.

Example data

1	Rep	Target	Actual
2	Ana	50000	61200
3	Ben	45000	43800
4	Chloé	55000	58900
5	Dev	40000	47250
6	Eun-ji	60000	52400

Common mistakes

MAX returns 0
The range holds no real numbers, often because they're stored as text. MAX of nothing is 0, not an error, so check the data rather than trusting the result.
Using MAX with a condition
MAX(C2:C6) can't filter by region or rep. Use MAXIFS, like =MAXIFS(C2:C9,A2:A9,"North").
Wanting the second-highest value
MAX only gives the top value. Use LARGE(range,2) for the second highest, LARGE(range,3) for the third, and so on.
An error in the range
If any cell in the range shows an error like #N/A, MAX returns that error too. Fix the source cell, or use AGGREGATE to skip errors.

FAQ

How do I find the name that goes with the highest value?+

Look up the MAX: =XLOOKUP(MAX(C2:C6),C2:C6,A2:A6). In older Excel use =INDEX(A2:A6,MATCH(MAX(C2:C6),C2:C6,0)).

Does MAX work with dates?+

Yes. Dates are numbers, so MAX returns the latest date. Format the result cell as a date to read it.

How do I stop a formula going below zero?+

Wrap it in MAX with 0: =MAX(0,C2-B2). Whenever the calculation is negative, 0 is larger, so 0 is returned.

Lesson

Master MAX step by step: AVERAGE, MIN and MAX

Summarize a list with its average, smallest and largest values.

Related functions

MIN AVERAGE XLOOKUP SUM