Math function

Excel MAX function

Q: How do I find the max value with criteria in Excel?

Use MAXIFS, for example =MAXIFS(C2:C10,B2:B10,"Airport"). It returns the largest value in rows that match.

Q: How do I get the name next to the highest value?

Use =INDEX(names,MATCH(MAX(values),values,0)), or XLOOKUP(MAX(values),values,names) in newer Excel.

MAX returns the biggest number from the cells you give it. Use it to find your best sales day, the highest score, or the latest date in a list. Text and empty cells are ignored.

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

Arguments

number1: The first number, cell or range to check.
number2: Optional. More numbers, cells or ranges, up to 255.

MAX examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C
1	Date	Store	Sales
2	2025-03-03	Downtown	2480
3	2025-03-04	Downtown	3125
4	2025-03-05	Airport	1960
5	2025-03-06	Airport	3410
6	2025-03-07	Mall	2875

=MAX(C2:C6)
Result: 3410
The best sales day in the week.
=MAX(C2:C6)-MIN(C2:C6)
Result: 1450
Subtract MIN from MAX to get the spread between the best and worst day.
=MAX(0,C4-2500)
Result: 0
A handy trick: MAX(0, ...) stops a result going below zero. Airport sales missed a 2500 target, so this shows 0, not a negative.
=INDEX(B2:B6,MATCH(MAX(C2:C6),C2:C6,0))
Result: Airport
Pair it with INDEX and MATCH to name the store with the top day.

How to use MAX

Click an empty cell.
Type =MAX(
Select the range to check.
Close the bracket and press Enter.

Common MAX mistakes

Numbers stored as text

MAX skips text in a range, so a big value stored as text is invisible to it. Convert the column to numbers.

Wanting the top 3, not just the top 1

MAX only gives the single largest. Use LARGE(range,2) and LARGE(range,3) for the runners-up.

Result shows as a strange number

If you MAX a date column, the answer is a date serial like 45723. Format the cell as a date.

MAX FAQ

How do I find the max value with criteria in Excel?

Use MAXIFS, for example =MAXIFS(C2:C10,B2:B10,"Airport"). It returns the largest value in rows that match.

Why does MAX return 0?

The range has no real numbers in it, usually because they are stored as text. MAX returns 0 when it finds nothing to compare.

How do I get the name next to the highest value?

Use =INDEX(names,MATCH(MAX(values),values,0)), or XLOOKUP(MAX(values),values,names) in newer Excel.