MIN, MAX, LARGE and SMALL in Excel: Highest and Lowest
· 9 min read
To find the lowest value in a range, use MIN. For the highest, use MAX:
=MIN(C2:C11)
=MAX(C2:C11)Both skip empty cells and text. When you need the second or third highest instead, LARGE and SMALL take a position: =LARGE(C2:C11, 2) is the second largest value and =SMALL(C2:C11, 2) the second smallest. For the highest or lowest value that meets a condition, such as the cheapest product in one category, use MINIFS and MAXIFS.
How to find min and max in Excel
Ten products from the Brightline Supply Co. catalogue, with their list prices. The questions in column E are answered by the formulas in column F:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | List price | Question | Result | |
| 2 | Copy Paper A4 (500) | Paper & Print | $5.50 | Lowest price | $5.50 | |
| 3 | Toner Cartridge Black | Paper & Print | $59.00 | Highest price | $84.00 | |
| 4 | Toner Cartridge Color | Paper & Print | $84.00 | Second highest | $59.00 | |
| 5 | Espresso Beans 1kg | Coffee & Kitchen | $24.00 | Cheapest product | Copy Paper A4 (500) | |
| 6 | Paper Cups (1000) | Coffee & Kitchen | $36.00 | Third lowest | $11.00 | |
| 7 | Sugar Sticks (1000) | Coffee & Kitchen | $11.00 | Highest in Cleaning | $25.00 | |
| 8 | All-Purpose Cleaner 5L | Cleaning | $14.00 | Lowest in Coffee & Kitchen | $11.00 | |
| 9 | Paper Towels (24) | Cleaning | $25.00 | Price range | $78.50 | |
| 10 | Ballpoint Pens (50) | Desk Supplies | $9.50 | |||
| 11 | Stapler Heavy Duty | Desk Supplies | $22.00 |
The minimum function in Excel returns $5.50 (Copy Paper A4) and MAX returns $84.00 (Toner Cartridge Color). Their difference, the price range in F9, is $78.50.
MIN and MAX accept several ranges and single values too, separated by commas: =MAX(C2:C11, E2:E11) looks across two columns, and =MIN(C2:C11, 5) never returns more than 5.
Which product has the lowest value?
MIN gives you the price, not the product. To get the name next to it, look the value up. F5 does it with INDEX and MATCH:
=INDEX(A2:A11, MATCH(F2, C2:C11, 0))MATCH finds the row where the price equals the minimum, and INDEX returns the product name from that row: Copy Paper A4 (500). With XLOOKUP the same thing reads =XLOOKUP(MIN(C2:C11), C2:C11, A2:A11). If two products share the lowest price, both formulas return the first one in the list. Our INDEX MATCH guide explains the lookup in detail.
LARGE and SMALL: the 2nd, 3rd or nth value
The Excel LARGE function returns the value at a position counted from the top, and SMALL counts from the bottom:
=LARGE(array, k)
=SMALL(array, k)=LARGE(C2:C11, 1) is the same as MAX, and =SMALL(C2:C11, 1) is the same as MIN. In the sheet above, the second highest price is $59.00 (F4) and the third lowest is $11.00 (F6). The sorted prices run 5.50, 9.50, 11.00, 14.00 and so on, so the third from the bottom is the Sugar Sticks.
If k is larger than the number of values, both functions return #NUM!. There is no 11th highest of ten prices.
Top N with LARGE
The classic pattern for a top 3 list types the positions 1, 2 and 3 in a column, then points LARGE at them. One formula fills down instead of three formulas with a hard-coded 1, 2 and 3:
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Revenue | Position | Top revenue | Customer | Question | Result | |||
| 2 | Harbor & Pine Hotels | 48,210 | 1 | 48,210 | Harbor & Pine Hotels | Lowest, MIN | 0 | |||
| 3 | River Print Co | 31,540 | 2 | 31,540 | River Print Co | Lowest above zero | 12,380 | |||
| 4 | Willow Logistics | 31,540 | 3 | 31,540 | River Print Co | Customers with a number | 7 | |||
| 5 | Willow Hotels | 27,905 | ||||||||
| 6 | Granite Accounting | |||||||||
| 7 | Copper Architects | 12,380 | ||||||||
| 8 | Maple Motors | 0 | ||||||||
| 9 | Atlas Realty | 19,760 |
F2 holds =LARGE($B$2:$B$9, E2), with the range locked by $ signs so it stays put as the formula fills down while E2 moves to E3 and E4. The top three are 48,210, 31,540 and 31,540. =SMALL($B$2:$B$9, E2) filled down the same way would return the three lowest: 0, 12,380 and 19,760.
Two details in this small table cause most of the surprises with these functions.
Ties and blanks
LARGE and SMALL do not skip duplicates. River Print Co and Willow Logistics both have 31,540, so that value fills positions 2 and 3. That is usually right for a "top 3 values" question. If you want the second highest distinct value, remove duplicates first. In Microsoft 365, =LARGE(UNIQUE(B2:B9), 2) does that.
The names in column G show the knock-on problem. MATCH returns the first row that holds 31,540, so G3 and G4 both say River Print Co, and Willow Logistics disappears from the list. The fix is a tie-breaking rank, covered with a worked example in our RANK function guide. In Microsoft 365 you can also sort whole rows and keep the top three, which keeps every name with its own value:
=TAKE(SORT(A2:B9, 2, -1), 3)Empty cells are ignored, zeros are not. Granite Accounting's empty cell does not count as a value: COUNT in J4 returns 7, not 8. Maple Motors' 0 is a real number, so MIN in J2 returns 0 and SMALL puts it first. Whether that is right depends on the question. If zero means "no sales" and you want the lowest real figure, exclude it with MINIFS:
=MINIFS(B2:B9, B2:B9, ">0")Result: 12,380 (Copper Architects). The range to take the minimum of and the range to test are the same column here, which is allowed.
Text is ignored too, including numbers stored as text. If a revenue figure arrived as the text "52000" from an export, MAX skips it without warning. A COUNT that is lower than the number of rows is the clue.
An error anywhere breaks the result. If one cell in the range is #N/A, MIN, MAX, LARGE and SMALL all return #N/A. Fix the source if you can. If you cannot, AGGREGATE can ignore errors: =AGGREGATE(14, 6, B2:B9, 1) is the largest value (function 14 is LARGE, option 6 skips errors) and =AGGREGATE(15, 6, B2:B9, 1) is the smallest.
MINIFS and MAXIFS: min and max with conditions
MINIFS and MAXIFS work like SUMIFS: the range to return a value from comes first, then pairs of criteria range and criteria.
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=MINIFS(min_range, criteria_range1, criteria1, ...)In the product sheet, =MAXIFS(C2:C11, B2:B11, "Cleaning") returns $25.00 (Paper Towels) and =MINIFS(C2:C11, B2:B11, "Coffee & Kitchen") returns $11.00 (Sugar Sticks). To build a small table with one row per category, put the category names in a column and point the criteria at them:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | List price | Category | Highest | Lowest | |
| 2 | Copy Paper A4 (500) | Paper & Print | $5.50 | Paper & Print | $84.00 | $5.50 | |
| 3 | Toner Cartridge Black | Paper & Print | $59.00 | Coffee & Kitchen | $36.00 | $11.00 | |
| 4 | Toner Cartridge Color | Paper & Print | $84.00 | Cleaning | $25.00 | $14.00 | |
| 5 | Espresso Beans 1kg | Coffee & Kitchen | $24.00 | Desk Supplies | $22.00 | $9.50 | |
| 6 | Paper Cups (1000) | Coffee & Kitchen | $36.00 | ||||
| 7 | Sugar Sticks (1000) | Coffee & Kitchen | $11.00 | ||||
| 8 | All-Purpose Cleaner 5L | Cleaning | $14.00 | ||||
| 9 | Paper Towels (24) | Cleaning | $25.00 | ||||
| 10 | Ballpoint Pens (50) | Desk Supplies | $9.50 | ||||
| 11 | Stapler Heavy Duty | Desk Supplies | $22.00 |
Paper & Print runs from $5.50 to $84.00, Coffee & Kitchen from $11.00 to $36.00, Cleaning from $14.00 to $25.00 and Desk Supplies from $9.50 to $22.00. Criteria work the same way as in COUNTIFS, so ">=20", "<>Cleaning" and wildcards such as "Paper*" are all allowed. Our guide to greater than or equal to in Excel covers the operators.
One trap: when no row matches, MINIFS and MAXIFS return 0, not an error. A misspelt category ("Cleanning") gives a lowest price of $0.00, which looks like a real answer. If a zero would be misleading, check the count first:
=IF(COUNTIFS(B2:B11, E2)=0, "No products", MINIFS(C2:C11, B2:B11, E2))MINIFS and MAXIFS are in Excel 2019 and later, Microsoft 365 and Excel for the web. In older versions, =MAX(IF(B2:B11="Cleaning", C2:C11)) gives the same answer, entered with Ctrl+Shift+Enter.
Dates, caps and floors
Earliest and latest dates. Dates are numbers in Excel, so =MIN(D2:D500) on a column of order dates returns the first order date and =MAX(D2:D500) the most recent. Format the result cell as a date, or you will see a number such as 45658 (1 January 2025).
Cap a value with MIN. A rebate of 5% of the order value, but never more than $250: =MIN(E2*5%, 250). MIN picks the smaller of the two, so the rebate stops growing at 250.
Stop a value going below zero with MAX. The shortfall against a target should not be negative when the target is beaten: =MAX(0, C2-B2), where C2 is the target and B2 the actual, returns 0 once the target is met.
Common mistakes
Treating a zero from MINIFS as a real minimum. No matching rows, a typo in the criteria or an extra space in the category all give 0. Compare with COUNTIFS before you trust it.
Hard-coding k. Writing =LARGE(B2:B9,1), =LARGE(B2:B9,2) and =LARGE(B2:B9,3) by hand works, but it breaks when someone asks for a top 5. Put the positions in cells and reference them.
Forgetting the $ signs. Without them, a filled-down =LARGE(B2:B9, E2) becomes =LARGE(B3:B10, E3) on the next row and skips the first value. See absolute references.
Not checking which end of the list you got. A top-N formula written by an AI assistant can use SMALL where LARGE was meant, or point at a column where "biggest" is stored differently than you expect, such as refunds recorded as negative amounts. Nothing errors, and the list looks plausible. Before you share it, check the first result against a value you can see is the biggest in the column.
Expecting a name from MIN or MAX. They return the value only. Add INDEX MATCH or XLOOKUP to get the product or customer, and watch for ties.
FAQ
How do I find the min and max in Excel?
Use =MIN(range) for the lowest value and =MAX(range) for the highest, for example =MIN(C2:C11) and =MAX(C2:C11). Both ignore empty cells and text.
How do I find the second highest value in Excel?
Use =LARGE(range, 2). LARGE returns the value at any position from the top, so =LARGE(range, 3) is the third highest.
What is the difference between SMALL and MIN?
=SMALL(range, 1) and =MIN(range) return the same value. SMALL also returns the 2nd, 3rd or nth smallest value when you change the second argument.
How do I find the lowest value with a condition?
Use MINIFS, for example =MINIFS(C2:C11, B2:B11, "Cleaning"). It returns 0 when no row matches, so check with COUNTIFS if that could happen.
How do I find the lowest value excluding zero?
Use =MINIFS(B2:B9, B2:B9, ">0"). It applies the condition to the same column it takes the minimum from.
Does MIN ignore blank cells?
Yes. MIN, MAX, LARGE and SMALL ignore empty cells and text, but they do include zeros. A range with no numbers at all gives 0 from MIN and MAX.
How do I get the average as well?
AVERAGE works on the same range. See our average formula guide, which also compares the average with the median.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Average, biggest, smallest” →