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.

Excel CSE · 2 min read
In this guide
  1. Syntax
  2. Basic example
  3. MINIFS and MAXIFS
  4. Who has the max?
  5. Related tricks
  6. FAQ

Syntax

=MIN(number1, [number2], …) =MAX(number1, [number2], …)

Both ignore empty cells, text and TRUE/FALSE in a range.

Basic example

ABC
1RegionRepSales
2NorthAnna1,200
3SouthBen800
4NorthChris950
5EastDana450
6SouthEli700
  • =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.

  • 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").