跳到正文
DEV Community· SheetError·· 11 天前AI 評分52

Google Sheets 的 FILTER 出現 #N/A:兩種成因,先看工具提示

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

AI 導讀

Google Sheets 的 FILTER 公式出現 #N/A,可能是沒有資料列符合條件,也可能是資料範圍與條件範圍的列數不一致;工具提示可分辨兩者。IFNA 和 IFERROR 會掩蓋這兩種情況,因此應先修正範圍列數,再為無匹配結果加入 IFNA。

正文

SheetError

=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)

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)

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")

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.

來源:DEV Community · dev.to