About this article
This article was created using an automated generation workflow utilizing generative AI. After checking the Microsoft Support specification for the FILTER function, we organized how to explicitly handle the case of 0 results using the third argument with dummy data.#CALC!using the third argument.
Verification Status: 📘 Confirmed with official Microsoft specifications, not verified on actual Excel hardware.
Excel's FILTER function becomes easier to handle in tables where search results may yield 0 matches if you keep the third argument [if_empty] in mind.
Try it first
Enter the following in A2:B4.
| Department | Count |
|---|---|
| Sales | 3 |
| General Affairs | 2 |
| Development | 5 |
Run it in a separate cell.
=FILTER(A2:B4,A2:A4="営業","該当なし")
Confirm that the row for Sales is returned.
Change one part
Change only the condition to "法務".
=FILTER(A2:B4,A2:A4="法務","該当なし")
This time, 該当なし is returned. Microsoft's official documentation states that if the third argument is omitted and an empty array cannot be returned, it results in #CALC!.
Empty strings and "Not Found" are different
If you set the third argument to "", you can make it look blank. However, in reports or subsequent functions where you want to distinguish between "0 results" and "not yet entered," using an explicit string can make auditing easier.
For practical work
This is useful when you want to treat 0 results as a normal state in lists by person, unprocessed issue extraction, inventory tables, inquiry lists, and more. On the other hand, if errors like #N/A are included on the include side, the FILTER itself will result in an error, so quality checks of the source data are separately required.
