Excel FILTER Function Returning #N/A? Fixes That Work

Errors & fixes · Updated November 19, 2026

If Excel’s FILTER function shows #N/A, the error usually comes from an error value in the data or the include criteria—not simply from having no matching rows. In Microsoft 365, FILTER normally returns #CALC! when nothing matches and no fallback is supplied, so check the exact error and the formula’s inputs before changing it.

What FILTER errors mean

FILTER returns selected rows or columns from an array based on a TRUE-or-FALSE include condition. It is available in Microsoft 365 and Excel 2021 or later, including current Excel for the web and Excel for Mac. The function produces a dynamic array: Excel places the results in the formula cell and spills them into nearby cells.

There are several different failures that can look like “FILTER is broken,” but they point to different causes:

  • #N/A usually means an input or calculation used by FILTER contains the not-available error. A lookup inside the include condition may return it, or a selected source cell may already contain it.
  • #CALC! commonly appears when the condition finds no rows and the optional third argument is omitted.
  • #VALUE! can occur if the include array does not have a compatible shape with the data array, such as using a condition range with the wrong number of rows.
  • #SPILL! means Excel cannot place the results because something blocks the spill area, or because the formula is entered in a location that does not allow the required spill.

These distinctions matter: adding an empty-result message can address #CALC!, but it will not repair a lookup that returns #N/A or clear a blocked spill range.

First identify where #N/A enters the formula

Start by selecting the FILTER formula cell and inspecting the formula bar. Then examine each range used for the returned data and for the include condition. A cell may display #N/A because of a formula elsewhere, and FILTER can pass that error through when it evaluates the selected rows or the condition array.

For a quick check of one suspected cell, with the value or formula result in B2, use this formula in an empty cell:

=ISNA(B2)

It returns TRUE if B2 currently evaluates to #N/A, and FALSE otherwise. Repeat the check on cells in the include range as well as the data range. If the error comes from a lookup, inspect that lookup’s lookup value, lookup range, and exact-match setting; correcting the lookup is usually better than hiding its error in the FILTER formula.

Another useful test is to temporarily simplify the include condition. If a straightforward condition works but the original condition returns #N/A, the problem is likely inside one of the calculations used to build the original condition. Restore the parts one at a time so you can identify which expression introduces the error.

Handle the no-matching-rows case correctly

No matches are a normal outcome, not necessarily a faulty formula. FILTER’s optional third argument, often called the if-empty argument, lets you choose what to display when the include condition selects no rows. It can be a short message, a blank string, or another suitable value.

Suppose A2:C6 contains order number, region, and sales, while G2 contains the region to find. If G2 says North and no row has that region, this formula displays a message instead of the usual empty-array error:

With order data in A2:C6, regions in B2:B6, and the requested region in G2, use:

=FILTER(A2:C6,B2:B6=G2,"No matching rows")

If G2 is North and North is absent from B2:B6, the result is the text No matching rows. If G2 is West and two West records are present, FILTER returns those two full rows. The fallback does not fix an actual #N/A in the source or criteria; it only specifies what to return when there are zero qualifying rows.

A blank display is another option. With the same ranges, replace the message with an empty text value in the third argument. Be aware that a displayed blank is not the same as an unused cell: the formula still occupies its cell, and the result is a zero-length text value.

Check lookup-based include conditions

A frequent source of #N/A is a lookup used to decide which records FILTER should include. For example, MATCH returns #N/A when a lookup value is absent. If that lookup result is used directly in a condition array, FILTER may receive an error instead of a clean TRUE or FALSE for that row.

When the goal is to keep rows whose region appears in a permitted list, test the lookup result with ISNUMBER. With records in A2:C10, their regions in B2:B10, and the allowed regions in G2:G4, use:

=FILTER(A2:C10,ISNUMBER(MATCH(B2:B10,G2:G4,0)),"No matching regions")

MATCH returns a position for a found region and #N/A for one that is not on the list. ISNUMBER converts those outcomes into TRUE or FALSE, so unmatched regions are excluded instead of becoming errors in the include array. The formula returns the rows whose region occurs in G2:G4, or the message if none do.

This pattern is different from replacing every error with a message. It turns the expected “not on the allowed list” outcome into FALSE while still allowing you to investigate errors in the returned data. If a missing lookup value should signal a data-quality problem rather than simply exclude a row, fix or flag that lookup instead of suppressing it.

Look for errors in the rows being returned

Even when the include condition is valid, a selected row can contain a cell whose value is already #N/A. FILTER returns the selected cells as they are; it does not automatically repair errors in the source data. Check the exact row that qualifies and look across every column in the returned array, not only the column used to filter.

If the error is expected and you deliberately want to display a substitute for it, you can apply IFNA to the source array before filtering. With data in A2:C10 and the region test in B2:B10, the following replaces source #N/A values with blanks in the returned array:

=FILTER(IFNA(A2:C10,""),B2:B10=G2,"No matching rows")

Use that approach only when a blank is an honest substitute. It can make a broken lookup look like a valid empty field, which may confuse later analysis. If those errors indicate missing product codes, unmatched customer IDs, or incomplete records, retaining or separately flagging them is often safer than concealing them.

Verify range sizes and criteria

The include array must align with the array being filtered. If A2:C10 contains nine rows, a row-based condition normally needs to cover the same nine rows. A condition that covers only B2:B8 or extends through B11 is not an equivalent range. Make sure both ranges start and end on the same data rows, and check that you are filtering rows rather than accidentally supplying a column-shaped condition for a different orientation.

Multiple conditions can be combined with multiplication, which acts like AND for TRUE/FALSE tests: a row is included only when both tests are TRUE. With records in A2:C6, regions in B2:B6, sales in C2:C6, and the target region in G2, use:

=FILTER(A2:C6,(B2:B6=G2)*(C2:C6>0),"No matching rows")

For the sample records 1001 / West / 240, 1002 / East / 180, 1003 / West / 0, 1004 / East / 315, and 1005 / West / 90, with West in G2, the result is rows 1001 and 1005. Row 1003 is excluded because its sales value is zero. The multiplication combines two same-sized tests; it does not mean the sales amount is multiplied into the returned data.

Criteria can also fail to match for reasons that do not create an error. Extra spaces, inconsistent spelling, numbers stored as text, and dates stored as text can make visually similar values compare differently. For example, a region typed as West followed by a space may not equal the clean value in G2. Inspect the underlying values and formats rather than assuming that identical-looking cells are identical to Excel.

Check spill space and Excel version

A dynamic FILTER result needs room to spill. If cells below or beside the formula contain values, merged cells, or other obstacles, Excel may show #SPILL!, not #N/A. Clear the intended output area or move the formula to a location with enough space. A FILTER formula cannot spill into cells inside an Excel Table; place it outside the table or use a different layout for the results.

FILTER is available in Microsoft 365 and Excel 2021 or newer. Excel 2019 and Excel 2016 do not include the function as a native dynamic-array function, so a workbook opened in those versions may show an unsupported-function result such as #NAME? rather than #N/A. Excel for the web and current Mac editions support FILTER too, although labels and interface details can vary by platform. If a formula copied from a website is rejected, also check whether your regional settings expect semicolons instead of commas between arguments.

Why not wrap everything in IFERROR?

It can be tempting to put IFERROR around FILTER and display a friendly message for every failure. That may hide the symptom, but it also treats unrelated problems—such as a malformed range or an unexpected source-data error—as if there were simply no matching rows. The result can look plausible while important data is missing.

Prefer a targeted fix: use FILTER’s third argument for a genuine zero-match outcome, correct a lookup that should find a value, repair a range-size mismatch, or clear a blocked spill area. Use IFNA or IFERROR only when you have decided that the specific errors being caught are safe to replace. In particular, remember that a no-match FILTER result is usually #CALC!, so IFNA alone is not a no-match handler.

Frequently Asked Questions

Does FILTER return #N/A when there are no matches?
No. In current Excel, omitting the optional if-empty argument generally produces #CALC! when no rows match. #N/A usually points to an error in the source data or the include calculation, or to a fallback that itself evaluates to #N/A.

How do I show a message when FILTER finds nothing?
Supply a message as FILTER’s third argument. For example, use the formula shown in the no-matching-rows section and replace the ranges and criterion cell with the ones in your worksheet. This handles an empty result, not errors already present in the inputs.

Can FILTER return rows that contain an #N/A value?
Yes. If a selected source row contains #N/A, the error can appear in the corresponding output cell. Find and correct the source formula, or intentionally substitute a value with IFNA if hiding that error is appropriate for the task.

Why does FILTER show #VALUE! instead?
Check that the include condition has the same number of rows as the data array when filtering rows, or the same number of columns when filtering columns. Also inspect calculations used to build the condition for errors or incompatible array shapes.

Why does the formula work on one computer but not another?
The other computer may be running Excel 2019 or 2016, which does not support FILTER as a native dynamic-array function. Regional argument separators can also differ. Check the Excel version and, if necessary, use the separator required by that installation.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.