Home › Excel functions › MIN & MAX
MIN and MAX in Excel: Lowest and Highest Values
MIN returns the smallest number in a range and MAX the largest. With MINIFS and MAXIFS you can add conditions, and combined with a lookup you can find out who or what has the highest value.
MIN & MAX · In this guide
Syntax
=MIN(number1, [number2], …) =MAX(number1, [number2], …)Both ignore empty cells, text and TRUE/FALSE in a range.
Basic example
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | North | Anna | 1,200 |
| 3 | South | Ben | 800 |
| 4 | North | Chris | 950 |
| 5 | East | Dana | 450 |
| 6 | South | Eli | 700 |
=MAX(C2:C6)returns 1,200=MIN(C2:C6)returns 450
With conditions: MINIFS and MAXIFS
=MAXIFS(max_range, criteria_range1, criteria1, …)- Highest sale in the South:
=MAXIFS(C2:C6, A2:A6, "South")returns 800. - Lowest sale in the North:
=MINIFS(C2:C6, A2:A6, "North")returns 950.
Criteria work like in SUMIFS: text, ">100", wildcards or cell references. MINIFS and MAXIFS are available in Excel 2019 and later.
Find who has the highest value
MAX tells you the value, not the row. Combine it with a lookup to get the name:
=INDEX(B2:B6, MATCH(MAX(C2:C6), C2:C6, 0))returns Anna. In Microsoft 365 or Excel 2021+: =XLOOKUP(MAX(C2:C6), C2:C6, B2:B6). If two people share the top value, both formulas return the first one. See INDEX/MATCH.
Related tricks
- Second-highest or top 3: use LARGE and SMALL.
- Cap a value:
=MIN(B2, 100)never returns more than 100;=MAX(B2, 0)never goes below 0. - Latest or earliest date: dates are numbers, so
=MAX(D2:D100)returns the most recent date. - Highlight the max with conditional formatting › Top/Bottom Rules › Top 10 Items, set to 1.
Frequently asked questions
Why does MAX return 0?
The range contains no real numbers, often because the numbers are stored as text. Convert them and MAX will work.
How do I find the max ignoring errors?
Use AGGREGATE: =AGGREGATE(4, 6, C2:C6) returns the maximum while ignoring error values.
Does MIN ignore zeros?
No. To find the smallest value above zero, use =MINIFS(C2:C6, C2:C6, ">0").