The direct answer: to find the maximum value in a range while ignoring #N/A errors, use the array formula =MAX(IF(ISNA(range),"",range)) entered with Ctrl+Shift+Enter in older Excel, or the dynamic array formula =MAX(IFNA(range,"")) in Excel 365. For a simpler non-array alternative, use =AGGREGATE(4,6,range), where 4 means MAX and 6 ignores error values.
Why #N/A Errors Break MAX
Excel’s MAX function is designed to ignore text and logical values, but it does not ignore error values. If any cell in the range contains #N/A, #VALUE!, #DIV/0!, or any other error, MAX returns that same error. This is a common frustration when working with VLOOKUP, XLOOKUP, or MATCH results that return #N/A for missing items.
The AGGREGATE Function (Simplest Method)
If you’re using Excel 2010 or later, AGGREGATE is the cleanest solution. It combines the functionality of SUBTOTAL with the ability to ignore errors, hidden rows, and other conditions.
Syntax: =AGGREGATE(function_num, options, array)
function_num: 4 for MAX, 5 for MIN, 1 for AVERAGE, etc.options: 6 to ignore error values, 5 to ignore hidden rows, 7 to ignore both.array: the range to evaluate.
Example: With values in A2:A10, where A5 contains #N/A, the formula =AGGREGATE(4,6,A2:A10) returns the largest number ignoring the error. If the range contains only errors, it returns #NUM!.
Array Formula with IF and ISNA (Works in All Versions)
For older Excel versions (2007 and earlier), you must use an array formula. The classic approach replaces errors with an empty string, which MAX ignores.
With values in A2:A10, enter: =MAX(IF(ISNA(A2:A10),"",A2:A10))
Then press Ctrl+Shift+Enter (not just Enter). Excel will wrap the formula in curly braces {} in the formula bar. On a Mac, press Cmd+Shift+Enter.
If you have other error types besides #N/A, use ISERROR instead of ISNA: =MAX(IF(ISERROR(A2:A10),"",A2:A10)).
Using IFNA in Excel 365 and 2021
In Excel 365 and Excel 2021, dynamic arrays allow you to enter formulas normally without Ctrl+Shift+Enter. The IFNA function specifically handles #N/A errors:
=MAX(IFNA(A2:A10,""))
This works because IFNA returns an empty string for each #N/A in the range, and MAX ignores text. For all error types, use =MAX(IFERROR(A2:A10,"")).
Comparison of Methods
| Method | Excel Version | Pros | Cons |
|---|---|---|---|
| AGGREGATE | 2010+ | Simple, no array entry, ignores all errors | Not available in older versions |
| Array IF(ISNA) | All | Works everywhere | Requires Ctrl+Shift+Enter, can be slow on large ranges |
| IFNA/IFERROR | 365/2021 | Easy, dynamic arrays | Only in newer versions |
Common Problems and Solutions
- Formula returns 0 instead of a number: If all values are errors,
MAXwith empty strings returns 0. To avoid this, use=IFERROR(MAX(IFNA(A2:A10,"")),"No valid data"). - Array formula not working: Forgetting to press Ctrl+Shift+Enter will cause #VALUE!. Re-enter and press the right key combo.
- Ignoring hidden rows: Use
AGGREGATE(4,5,A2:A10)to ignore manually hidden rows. - Large data sets: Array formulas over thousands of cells can slow down Excel. Prefer AGGREGATE or a helper column.
Alternative: Helper Column
If you prefer not to use array formulas, add a helper column that cleans errors. For example, in B2 enter =IF(ISNA(A2),"",A2) and copy down. Then use =MAX(B2:B10). This is transparent and works in all versions, but it adds a column to your sheet.
Frequently Asked Questions
How do I ignore #N/A in MAXIFS?
MAXIFS (Excel 2019+) does not ignore errors by default. You can combine it with IF: =MAX(IF(ISNA(A2:A10),"",A2:A10)) with Ctrl+Shift+Enter, or use AGGREGATE with a condition: =AGGREGATE(4,6,IF(criteria_range=criteria,value_range)) as an array formula.
Does MAX ignore text and logical values?
Yes, MAX ignores text, logical values (TRUE/FALSE), and empty cells. It only fails when an error value is present.
What if I want to ignore only #N/A but not other errors?
Use ISNA instead of ISERROR in the array formula, or IFNA in Excel 365. For AGGREGATE, you cannot specify only #N/A; it ignores all errors.
Can I use MAX with VLOOKUP results that may be #N/A?
Yes. Wrap the VLOOKUP in IFNA: =MAX(IFNA(VLOOKUP(range,...),"")) in Excel 365, or use AGGREGATE on the range of VLOOKUP results.
Why does my formula return #NUM! when all cells are errors?
AGGREGATE returns #NUM! if the array contains no numbers. Use IFERROR around it to display a custom message.
Final Tips
Always test your formula with a small sample that includes an error. For financial or critical data, double-check that ignoring errors is appropriate—sometimes you want to know about errors rather than hide them. If you need to audit, keep the original data intact and use a separate column for the cleaned version.
Related
- Free Excel Expense Tracking Sheet Template
- Excel Eye Center: Services, Costs & What to Expect
- Excel Employee Rotation Schedule Template Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Counting Unique Text ValuesThe fastest way to count unique text values in Excel is =SUMPRODUCT((COUNTIF(range,range)>0)/COUNTIF(range,range)) for Excel 2019 and earlier, or…
- Using IFS Formula in Excel: Syntax and ExamplesIn Excel, use the IFS function to test conditions in order and return a value for the first…
- Excel COUNTIF Contains Partial Text: Formula GuideThe formula to count cells containing partial text in Excel is =COUNTIF(range,"*text*"). Replace range with your cell range…
- Excel Sum Every Other Column: Formulas and ExamplesTo sum every other column in an Excel row, use =SUMPRODUCT((MOD(COLUMN(B2:K2)-COLUMN(B2),2)=0)*B2:K2). With numbers in B2:K2, this adds B2,…
- Excel Formula Symbols: What Each Operator MeansExcel formula symbols are operators and reference markers that tell Excel what to calculate or which cells to…
- Excel Filter Duplicates Across Multiple ColumnsYes. In Excel for Microsoft 365 on Windows, add a helper column that counts how often each row’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.