When FILTER fails in Google Sheets, it either shows an error or, worse, returns rows that look plausible but are wrong. We broke FILTER on purpose in a test sheet and wrote down the exact message or result for each cause. Find your symptom below.
The examples use the orders table from our FILTER with multiple conditions guide: dates in A2:A15, region in B, rep in C, product in D, status in E and amount in F.
Tested in Google Sheets. We reproduced each of these cases on November 17, 2026 and copied the error messages exactly as Sheets showed them. How we test
Quick diagnosis
| Symptom | Likely cause |
|---|---|
| #N/A “No matches are found in FILTER evaluation.” | No row meets the conditions (causes 1, 6, 7, 8 and 9) |
| #N/A “FILTER has mismatched range sizes.” | Ranges of different sizes (2) or AND/OR inside FILTER (3) |
| #REF! “Array result was not expanded…” | Something is in the way of the results (4) |
| No error, but the wrong rows | Shifted ranges (5), text numbers (7), dates with times (8), empty rows (9) |
1. No row matches the conditions
Message: #N/A, “No matches are found in FILTER evaluation.” This is the normal result when nothing matches, for example =FILTER(A2:A15, B2:B15="Central") on a table with no Central region. If you expect empty results sometimes, show a message instead:
=IFERROR(FILTER(A2:A15, B2:B15="Central"), "No orders")
If you’re sure some rows should match, the cause is usually in the data. Causes 6 to 9 below all produced this message or missing rows in our test.
2. The ranges have different sizes
Every condition must have the same number of rows as the range you filter. In =FILTER(A2:A15, B2:B14="North"), the range has 14 rows and the condition 13. Message: #N/A, “FILTER has mismatched range sizes. Expected row count: 14. column count: 1. Actual row count: 13, column count: 1.”
Note that it’s #N/A, not #VALUE!. The message tells you the expected size and the actual one, so you can see which range is wrong. It often happens after you edit one range and forget the others. Using whole-column ranges such as A2:A and B2:B everywhere avoids it.
3. AND() or OR() inside FILTER
This looks natural, but it doesn’t work:
=FILTER(A2:A15, AND(B2:B15="North", E2:E15="Paid"))
Message: “FILTER has mismatched range sizes. Expected row count: 14. column count: 1. Actual row count: 1, column count: 1.” AND and OR combine a whole range into a single TRUE or FALSE, so FILTER receives one value instead of 14. OR gave exactly the same error. Join the conditions like this instead:
- AND: separate arguments,
B2:B15="North", E2:E15="Paid", or multiplication,(B2:B15="North")*(E2:E15="Paid"). - OR: addition,
(B2:B15="North")+(B2:B15="South").
The same rule applies inside ARRAYFORMULA, as we found when testing ARRAYFORMULA with IF.
4. #REF! because something is in the way
FILTER writes its results into the cells below and to the right of the formula. If one of those cells already has content, you get #REF!: “Array result was not expanded because it would overwrite data in K3.” We put the word “note” in K3, two cells below a FILTER that needed four rows.
The message names the blocking cell. Clear it, or move the formula somewhere with enough empty space. When we replaced “note” with a single space, the cell looked empty but the #REF! stayed, with the same message.
5. Wrong rows because the ranges are shifted
This one gives no error at all. The ranges below are the same size, but the condition starts one row higher:
=FILTER(A2:A15, B1:B14="North")
It returned rows 3, 5, 7 and 15, each one the row after a North order. FILTER pairs the first row of each range, so the result shifts by one row. It usually happens when one range includes the header and the other doesn’t. Check that all the ranges start on the same row.
6. Extra spaces and upper or lower case
In our data, one order had the region “North ” with a trailing space. B2:B15="North" didn’t return it. The comparison ignores case, so “north” was returned. Two fixes, depending on what you want:
TRIM(B2:B15)="North"ignores extra spaces. It returned all five North rows, including “North ” and “north”.EXACT(B2:B15, "North")respects case and spaces. It returned only the three rows typed exactly “North”.
Spaces that TRIM can’t remove, such as the non-breaking space from copied web data, need the fixes in our remove extra spaces guide.
7. Numbers stored as text
One amount, 250, was stored as text. Three things went wrong, none with a clear error:
F2:F15=250didn’t find it, and FILTER returned “No matches are found in FILTER evaluation.”F2:F15>300returned it. When Sheets compares text with a number, the text counts as greater.SUM(FILTER(F2:F15, B2:B15="South"))gave 120 instead of 370, because SUM skips text.
Converting inside the condition, VALUE(F2:F15)>200, returned the right three rows. To fix the column itself, see convert text to numbers. A quick way to spot them: numbers stored as text are usually aligned to the left.
8. Dates: times and text dates
Two date problems:
- The last day is missing.
A2:A15<=DATE(2026,3,31)left out an order dated March 31 at 18:30, because a date with a time is later than the date alone. UseA2:A15<DATE(2026,4,1), the day after the end date. - Nothing matches.
A2:A15>="2026-02-01", with the date in quotes, returned “No matches are found in FILTER evaluation.” In quotes, the date is text, not a date. UseDATE(2026,2,1)orDATEVALUE("2026-02-01"). The DATEVALUE version returned the 10 expected rows.
If the dates in your column are themselves text, for example imported as dd/mm/yyyy, no comparison will work until you convert them. Our guide to sorting dd/mm/yyyy dates covers how to check and fix them.
9. Wildcards and empty rows
- Wildcards don’t work.
D2:D15="*an*"found no rows. FILTER compares the text literally. UseISNUMBER(SEARCH("an", D2:D15))for “contains”. It found the three Banana orders. - “Not equal” returns empty rows.
E2:E15<>"Refunded"returned 12 rows, including the empty row and an order with no status, because an empty cell isn’t “Refunded” either. Add a condition such asA2:A15<>""to skip empty rows. We cover this in detail in remove blank rows.
Found in testing: the most dangerous cases were the ones without an error. The shifted range, the text “250” passing a “>300” test and the missing order from March 31 all returned results that looked fine. When a FILTER matters, check its row count with =ROWS(FILTER(…)) against a quick COUNTIFS with the same conditions. Our COUNTIFS guide shows how to write them.
Still not working?
If the formula itself won’t save and you see “Formula parse error”, the problem is the syntax, not the data: see our guide to the formula parse error. To paste a formula and check its brackets and quotes, try the free formula checker.