Excel Max Value Based on Criteria: Formula Guide

Formulas & functions · Updated November 22, 2026

To find the maximum value in Excel based on one or more criteria, use MAXIFS (for Excel 2019 and Microsoft 365) or an array formula with MAX and IF (for older versions). For example, =MAXIFS(B2:B100, A2:A100, "East") returns the largest sales figure in column B where column A equals “East”.

Why MAXIFS Is the Modern Default

MAXIFS was introduced in Excel 2019 and is available in Microsoft 365. It works like SUMIFS or AVERAGEIFS: you give it a range to evaluate, then pairs of criteria ranges and conditions. The syntax is:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

For instance, with product names in A2:A20 and quantities in B2:B20, to find the largest quantity for “Widget”:

=MAXIFS(B2:B20, A2:A20, "Widget")

This returns 85 if the largest Widget quantity is 85. You can add more criteria: =MAXIFS(B2:B20, A2:A20, "Widget", C2:C20, ">10") finds the max quantity for Widgets with a price over 10. MAXIFS supports wildcards (* for any sequence, ? for a single character) and logical operators like ">=" or "<".

How to Use MAXIFS Step by Step

  1. Select the cell where you want the result.
  2. Type =MAXIFS(.
  3. Select the range that contains the values you want to find the max of (e.g., B2:B100).
  4. Select the first criteria range (e.g., A2:A100) and type the criteria (e.g., "East" or a cell reference like E1).
  5. Add more criteria ranges and conditions as needed, separating each pair with a comma.
  6. Close the parenthesis and press Enter.

On Mac, the steps are identical. In Excel for the web, MAXIFS works the same way. If you use Excel 2016 or earlier, MAXIFS is not available; use the array formula alternative below.

Array Formula Alternative for Older Excel

In Excel 2016 and earlier, you can use an array formula with MAX and IF. With the same ranges, enter:

=MAX(IF(A2:A20="Widget", B2:B20))

After typing, press Ctrl+Shift+Enter (Windows) or Command+Shift+Enter (Mac) to make it an array formula. Excel will show curly braces around it in the formula bar. This formula returns the maximum B value where A equals "Widget". For multiple criteria, nest IFs: =MAX(IF(A2:A20="Widget", IF(C2:C20>10, B2:B20))).

Array formulas can be slower on large datasets because they calculate over every cell in the range. If you have tens of thousands of rows, MAXIFS is more efficient. Also, array formulas require the Ctrl+Shift+Enter step; if you forget, you get a #VALUE! error or a wrong result.

Using MAXIFS with Cell References and Dates

Instead of typing criteria directly, reference a cell. For example, with a date in D1, to find the max sales after that date:

=MAXIFS(B2:B100, A2:A100, ">"&D1)

The ampersand (&) concatenates the operator with the cell value. For dates, ensure the cell contains a real date (not text). For text criteria with wildcards, "*Widget*" matches any cell containing "Widget". To match an exact blank or non-blank, use "" or "<>".

Common Problems and Fixes

  • #VALUE! error – Usually means you pressed Enter instead of Ctrl+Shift+Enter for an array formula, or your criteria range and max range have different sizes. Check that all ranges have the same number of rows.
  • Returns 0 instead of a value – This happens when no rows meet the criteria. MAXIFS returns 0 if no match, while an array formula returns 0 as well. To return something else, wrap in IFERROR: =IFERROR(MAXIFS(...), "No match").
  • Criteria with text and numbers – If your criteria column contains numbers stored as text, you may need to use "Widget" or "123" as text, or convert the column to numbers.
  • MAXIFS not available – If you see #NAME?, your Excel version is older than 2019. Use the array formula or upgrade.
  • Case sensitivity – MAXIFS is not case-sensitive by default. If you need case-sensitive matching, use an array formula with EXACT: =MAX(IF(EXACT(A2:A20,"widget"), B2:B20)).

Comparing MAXIFS with Other Functions

MAXIFS is the direct answer for conditional max. But sometimes you need a different approach:

  • MAX with FILTER (Microsoft 365) – =MAX(FILTER(B2:B20, A2:A20="Widget")) works but requires Microsoft 365 or Excel 2021. It is more flexible if you also want to return the row or other data.
  • SUMPRODUCT-based max – =SUMPRODUCT(MAX((A2:A20="Widget")*B2:B20)) is an old non-array formula that works in all versions, but it can be tricky with text and returns 0 if no match.
  • PivotTable – For quick analysis, a PivotTable with a Max value field can show the max per category without formulas, but it is not dynamic for a single cell lookup.

For finding the max across multiple columns with criteria, use MAXIFS with a range that spans multiple columns, but ensure the criteria ranges match the shape. For example, =MAXIFS(B2:C20, A2:A20, "Widget") returns the max from B2:C20 where A equals "Widget".

Frequently Asked Questions

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

Use MAXIFS with multiple criteria pairs. For example, =MAXIFS(B2:B20, A2:A20, "Widget", C2:C20, ">10") finds the max B where A is "Widget" and C is greater than 10. Each criteria range must be the same size as the max range.

Can I use MAXIFS with a cell reference for the criteria?

Yes, just reference the cell directly. For example, =MAXIFS(B2:B20, A2:A20, E1) uses the value in E1 as the criteria. For operators, concatenate: =MAXIFS(B2:B20, A2:A20, ">"&E1).

Why does MAXIFS return 0 when there is no match?

MAXIFS returns 0 if no row meets all criteria. To show a custom message, wrap it in IFERROR: =IFERROR(MAXIFS(...), "No match"). For an array formula, you can use =IF(COUNTIFS(...)=0, "No match", MAX(IF(...))).

Does MAXIFS work in Excel 2016?

No, MAXIFS was introduced in Excel 2019. In Excel 2016, use the array formula =MAX(IF(...)) with Ctrl+Shift+Enter, or use SUMPRODUCT as a non-array alternative.

How do I find the max value based on a date range?

Use two criteria: one for greater than or equal to the start date, one for less than or equal to the end date. Example: =MAXIFS(B2:B100, A2:A100, ">="&D1, A2:A100, "<="&D2) where D1 is start date and D2 is end date.

Can MAXIFS handle wildcards?

Yes, use * for any sequence and ? for a single character. For example, =MAXIFS(B2:B20, A2:A20, "W*") finds the max for any text starting with W. Wildcards work only with text criteria, not numbers.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.