DEV Community

SheetError
SheetError

Posted on Originally published at sheeterror.com

FILTER "No matches are found" #N/A in Google Sheets: two causes, read the tooltip first

#googlesheets #spreadsheets #tutorial #productivity

=FILTER(...) comes back as #N/A. The key you wanted might be missing, or it might be sitting right there. Both look like #N/A. The tooltip is what tells them apart.

Why it happens

FILTER uses #N/A for two different failures. No matches are found in FILTER evaluation means the formula ran and zero rows matched the condition. FILTER has mismatched range sizes means the data range and the condition are different heights, so FILTER never evaluates the condition at all. IFNA and IFERROR hide both, which is why you read the tooltip before wrapping anything.

Reproduce it in a minute

  1. New blank sheet. In A1 type sku, B1 name. A2 101, B2 Widget. A3 102, B3 Gadget. A4 103, B4 Lamp.
  2. In D2 enter: 
=FILTER(A2:B4,A2:A4=999)
Enter fullscreen mode Exit fullscreen mode

D2 shows #N/A. The tooltip is No matches are found in FILTER evaluation. Sku 999 isn't in the list, so that's a real empty result.

  1. In G2 enter: 
=FILTER(A2:B4,A2:A5=101)
Enter fullscreen mode Exit fullscreen mode

A2:B4 is three rows. A2:A5 is four. G2 is also #N/A, but the tooltip is FILTER has mismatched range sizes. Expected row count: 3. column count: 1. Actual row count: 4, column count: 1. Sku 101 is on the sheet. This isn't a miss.

The fix

For the empty result, wrap with IFNA:

=IFNA(FILTER(A2:B, A2:A=999), "none")
Enter fullscreen mode Exit fullscreen mode

That cell shows none. Leave the original D2 broken so you can still see the #N/A.

For the size error, don't wrap G2. Make the heights equal: =FILTER(A2:B4,A2:A4=101) spills 101 / Widget. Fix the ranges first. Then, if a later formula has zero matches, you can add IFNA.

Watch out for

  • IFNA hides the size bug too. IFNA(FILTER(A2:B4,A2:A5=101),"none") would show none even though 101 is in the list. Read the tooltip, fix mismatched heights, then wrap.
  • It's not a VLOOKUP type miss. Number-as-text or a trailing space is a different #N/A, covered on the VLOOKUP page. FILTER is not searching with a column index.
  • It's not #REF!. A UNIQUE overwrite, a circular loop, a VLOOKUP index past the table, or IMPORTRANGE waiting for Allow access are all #REF!, not this #N/A.
  • COUNTIFS with unequal heights is #VALUE!, not this #N/A. Don't copy a COUNTIFS size fix onto FILTER and expect the same error value.

Originally published on SheetError, a free library of Google Sheets error fixes (I run it).

This article was created with the help of AI.

Top comments (0)