Excel FILTER #VALUE! Error: Causes and Fixes

In Excel, FILTER most commonly returns #VALUE! when its include argument does not have the same number of rows or columns as the data being filtered. Make the two ranges the same shape, then check whether an error in the criteria or source data is causing the problem; adding FILTER’s optional “if empty” argument handles no matches, but does not fix a #VALUE! error.

What FILTER does—and what #VALUE! means

FILTER returns rows or columns from an array when corresponding values meet a condition. Its arguments are the data to return, a TRUE-or-FALSE include array, and an optional result to show when nothing meets the condition. It is a dynamic-array function: a single formula can return results in neighboring cells, called a spill range.

The key requirement is that the include array must line up with the dimension being filtered. When filtering rows, it needs one condition value for each row in the source array. When filtering columns, it needs one condition value for each column. If the dimensions do not match, Excel cannot pair conditions with the records, and the formula can return #VALUE!.

That is not the only possible source of the error. The condition expression itself may produce an error, or a referenced formula may already contain one. A useful diagnosis is to separate a range-size problem from an error being passed through from the workbook. Also distinguish #VALUE! from #CALC!, which can appear when FILTER finds no matching records and no “if empty” result was supplied.

Check the dimensions of the source and include ranges

Suppose the worksheet has names in A2:A6, regions in B2:B6, and sales in C2:C6. The source array A2:C6 contains five records. To filter those records by region, the include array must also cover five rows, such as B2:B6. The text criterion “East” produces one TRUE or FALSE value for each region.

With names in A2:A6, regions in B2:B6, and sales in C2:C6, use this formula to return all three columns for East-region records:

=FILTER(A2:C6,B2:B6="East","No matches")

For example, if the rows are Ana/East/120, Ben/West/90, Chen/East/75, Dia/East/140, and Eli/West/110, the result contains Ana, Chen, and Dia with their regions and sales. The optional third argument displays “No matches” if the criteria are valid but no row qualifies.

A common mistake is to select fewer cells for the condition than for the data. With names in A2:A6, regions in B2:B6, and sales in C2:C6, this formula compares a five-row array with a three-row condition range and can return #VALUE!:

=FILTER(A2:C6,B2:B4="East","No matches")

Change B2:B4 to B2:B6, or deliberately change the source array so both ranges cover the same records. Check the starting and ending row numbers, not just the column letters. If your source includes a header, avoid including it in only one of the two ranges.

Match the direction when filtering columns

The same shape rule applies across columns. If the array contains several rows and five columns, a condition for selecting columns needs one value per column. A vertical condition range will not correctly match a horizontal set of headers. For row filtering, compare heights; for column filtering, compare widths.

For example, a worksheet might have quarterly figures in B2:E10, with quarter names in B1:E1. To return columns whose heading is Q2, the array and condition should both span four columns. With figures in B2:E10 and quarter headings in B1:E1, use:

=FILTER(B2:E10,B1:E1="Q2","No Q2 column")

This returns the Q2 column across all data rows if the heading matches. In practice, the returned array and condition need compatible dimensions in the direction being filtered. If a formula is intended to filter records but instead selects a mismatched range of headers, reconsider whether the formula is filtering rows or columns.

Build multiple conditions with matching ranges

FILTER can apply more than one condition. Multiplying logical tests acts like AND: a record must satisfy both tests. Adding them acts like OR: a record can satisfy either test. Each test must cover the same rows as the source array. Parentheses make the intended order clear.

With names in A2:A6, regions in B2:B6, and sales in C2:C6, this formula returns East-region records with sales of at least 100:

=FILTER(A2:C6,(B2:B6="East")*(C2:C6>=100),"No matches")

For the sample data above, the result is Ana and Dia, because their East-region sales are 120 and 140. Chen is excluded because sales of 75 do not meet the second condition. Both condition ranges have five rows, matching A2:C6.

With names in A2:A6, regions in B2:B6, and sales in C2:C6, use addition for records from either East or West:

=FILTER(A2:C6,(B2:B6="East")+(B2:B6="West"),"No matches")

In this example, every record is either East or West, so all five records are returned. If you combine conditions from different-sized ranges, the combined include expression can itself cause a dimension error. Confirm the size of every range before combining them.

Look for errors in the criteria or source data

A correctly sized include array can still contain an error. For instance, if a region cell contains an error value, comparing that cell with “East” can produce an error instead of TRUE or FALSE. FILTER may then pass that error through rather than return an ordinary filtered result. Inspect the condition column for cells displaying errors, and inspect formulas used to create that column.

One option is to treat errors in the condition range as non-matches. This should be a deliberate choice: it prevents an error from stopping the filter, but it also hides the fact that the affected row had an invalid condition value. With names in A2:A6, regions in B2:B6, and sales in C2:C6, use:

=FILTER(A2:C6,IFERROR(B2:B6="East",FALSE),"No matching rows")

Here, an error arising from the region comparison becomes FALSE, so that record is excluded. This does not repair the source data. If the error indicates a misspelled lookup, missing input, or broken formula, correcting its cause is usually preferable to suppressing it. Also remember that errors in returned data cells are different from errors in the include array: a matching row may legitimately bring an existing source error into the output.

Do not confuse no matches or spill errors with #VALUE!

If a valid condition finds no records, FILTER without its third argument can return #CALC! because an empty array is not displayed as a normal result. Provide a meaningful “if empty” value when that outcome is possible. With names in A2:A6, regions in B2:B6, and sales in C2:C6, this formula displays a message when there are no North-region records:

=FILTER(A2:C6,B2:B6="North","No North-region records")

The message is not a general error handler. If the include range has the wrong size or the criteria expression produces #VALUE!, FILTER can still return an error. Similarly, an output area blocked by existing content or merged cells is a spill problem, normally shown as #SPILL!, rather than evidence that the include range has the wrong dimensions. Clear the intended spill area or move the formula if Excel reports a spill obstruction.

Another misconception is that the optional third argument means “catch any error.” It only supplies a result for the no-results case. Use it to make a report easier to read, not as a substitute for checking mismatched ranges or faulty source formulas.

Use IFERROR carefully

Wrapping the entire FILTER formula in IFERROR can replace an error with a friendlier message, but it can also hide unrelated problems, including a bad reference or a genuine data error. If you choose to do this, make the message actionable and keep a way to investigate the underlying cause. With names in A2:A6, regions in B2:B6, and sales in C2:C6, this formula returns a diagnostic message for any error from FILTER:

=IFERROR(FILTER(A2:C6,B2:B6="East","No matches"),"Check ranges and source data")

The output may look cleaner, but the message does not identify whether the issue was a range mismatch, an error in a condition, or another formula problem. During troubleshooting, removing the outer IFERROR is often useful because it lets Excel display the original error. Once the formula is correct, decide whether a general fallback is appropriate for the finished worksheet.

A practical troubleshooting sequence

Work from the structure outward instead of changing several parts of the formula at once. These checks usually narrow down a #VALUE! result quickly:

Identify the source array and the include expression in the formula. Confirm which one is meant to filter rows or columns.
Count the rows or columns in the source and each condition range. Make their relevant dimensions agree, including the first and last row.
Test the condition by itself in a spare cell or inspect its source cells for existing errors. Check that text comparisons use the expected spelling and that numeric conditions refer to numeric values.
Temporarily simplify multiple conditions to one. If the single-condition formula works, add the other tests back one at a time and verify their range sizes.
Check the result type. No matching rows may cause #CALC! without an “if empty” value; blocked output may cause #SPILL!; neither has the same fix as a dimension mismatch.
After fixing the cause, decide whether to retain an “if empty” message or a carefully scoped error handler.

For larger workbooks, using the same start and end rows in the source and condition ranges makes formulas easier to audit. Excel Tables can also help keep related columns aligned as rows are added, although the structured references still need to refer to the intended columns and records.

Availability and platform notes

FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. In Microsoft 365 for Windows, enter the formula in a cell and press Enter; results spill into adjacent cells when space is available. Excel for Mac and Excel for the web also support dynamic-array formulas, though interface details and update timing can differ. If a workbook is opened in an older Excel version that does not support FILTER, it may display a compatibility or function-name problem rather than behave like a current dynamic-array workbook.

Excel may use localized function names or different list separators depending on language and regional settings. The examples here use English function names and commas, as in a standard English-language Microsoft 365 setup. If Excel rejects otherwise sound syntax, check the separators required by that installation. Do not change range dimensions to compensate for a regional syntax issue.

Frequently Asked Questions

Why does FILTER return #VALUE! when the ranges look right?
Check the exact dimensions and the condition expression. A hidden difference in the first or last row can make the include array a different height, and an error in a referenced condition cell can also propagate. Test the condition separately and verify every range boundary.

Does the if_empty argument fix #VALUE!?
No. It supplies a result when the filter has no matching items. It does not correct mismatched dimensions or errors in the include calculation. A no-match situation without that argument is commonly associated with #CALC!, not #VALUE!.

Can FILTER return multiple columns?
Yes. Set the first argument to the columns you want returned and provide an include array aligned with the rows or columns you intend to select. When selecting rows from a multi-column table, the include array normally has one value per source row.

Why do I see #SPILL! instead of #VALUE!?
#SPILL! usually means Excel cannot place the dynamic-array result in the required cells, often because something occupies part of the output range. Clear or relocate the obstructing content. It is a different issue from a mis-sized include range.

Can I use IFERROR to hide a FILTER error?
You can, but a broad IFERROR wrapper may conceal a broken reference or bad source data as well as a range mismatch. Diagnose the underlying error first, then use a specific, informative fallback only if hiding that error is appropriate for the worksheet.

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.