About This Article
This article is created using an automated generation workflow utilizing generative AI. Microsoft's official FILTER function and #SPILL! documentation were verified on November 4, 2026, and dummy product data is used to compare "0 matches" with "output range obstructed." Formula evaluation on an actual Excel instance has not been performed.Verification Status: 📘 Official specifications confirmed. Evaluation on target Excel actual device not performed.
- Try This First: Compare 2 Matches, 0 Matches, and Spill Failure
- Supported Environments, Capabilities, and Limitations
- Minimal Verification Code and Formulas
- Zero Matches and Insufficient Output Space Are Different States
- Preparing with a Small Product Table
- The Three Arguments of FILTER
- Expected Results and Reading the Output
- Concept of Spilling
- What to Check When Encountering #SPILL!
- Difference Between Empty Strings and "No Match"
- Things to check before business use
- When applying to work
- When passing no-match strings to another formula
- An experiment to identify spill destination obstacles
- When there are errors in the search criteria values
- Official and primary sources
- GitHub Sample
Try This First: Compare 2 Matches, 0 Matches, and Spill Failure
Use only a new practice workbook. Through the following three operations, you can observe why the same FILTER formula yields different displays. Since actual device verification has not been performed, the following is based on official specificationsexpected results.
Enter the 4 rows of dummy product data described below into A2:C5, and set E2 to "Stationery". Enter the formula in F2 and verify whether the two rows P101 and P103 expand into F2:H3.
Change only E2 to "Clothing" and verify whether F2 becomes "No match". This is a change of search criteria, and the source data is not deleted.
After resetting E2 to "Stationery",G2 of the practice sheetenter "For verification". If F2 becomes #SPILL!, it is an output obstruction rather than a source data mismatch. Verify whether clearing the text in G2 restores the output.
| Condition | Expected Display | Cause |
|---|---|---|
| E2=Stationery, F2:H3 is empty | 2 rows × 3 columns | 2 matches |
| E2=Clothing | "None found" | 0 matches |
| E2=Stationery, text in G2 | #SPILL! | Spill range is blocked |
Note:Only edit G2 in a new practice sheet. Do not delete cells in existing operational workbooks.
Supported Environments, Capabilities, and Limitations
| Item | Prerequisite |
|---|---|
| Target | Designed for Excel for Microsoft 365, Excel 2021/2024, etc., which support the FILTER function. Enter four rows of dummy data into a new worksheet and observe the dynamic array results. |
| Topic | Verification Example of Distinguishing Between No Match and #SPILL! Using the Excel FILTER Function |
| Safety | Verify in a new practice workbook. Do not update or delete production data. |
| Constraints | Check Office version, macro settings, formula features, and data types in advance |
| Specification Verification Date | 2026-10-17 (JST) |
When the result of the FILTER function appears blank or returns #CALC! or #SPILL!, it may look like the same "no result" state. However, the troubleshooting steps differ between having 0 matching rows and being unable to spill because the destination cells are occupied. We will compare the relationship between the third argument, if_empty, and the spill range using a small table.
Minimal Verification Code and Formulas
You can test the following formula in a dummy data practice sheet. This is not an automated process that modifies values arbitrarily.
=FILTER(A2:C5,B2:B5=E2,"該当なし")
Compilation, execution, or formula evaluation on actual hardware has not been performed. Because display results depend on your individual Excel environment, please ensure the input conditions below are met before verifying.
Zero Matches and Insufficient Output Space Are Different States
The FILTER function returns an array of rows that meet the criteria. If no rows match, there is no data to display, but if existing values or merged cells are present in the output destination cells, the calculation results cannot be spilled. Both situations are similar in that you do not get the desired table on the screen, but the causes and solutions differ. Because the areas to inspect differ between searching the source data again when there are no matches and clearing the spill destination, it is important to identify the type of error first.
Preparing with a Small Product Table
Prepare four rows of dummy product data in cells A2:C5 of a new worksheet. Set column A as the product code, column B as the category, and column C as the unit price, and enter P101/Stationery/100, P102/Food/250, P103/Stationery/150, and P104/General/300. Enter "Stationery" in E2 as the category to search for, and leave potential output destination cells such as F2:H3 empty. Try this on a dedicated practice sheet without modifying existing workbooks.
The Three Arguments of FILTER
The syntax of the official Microsoft FILTER function is FILTER(array, include, [if_empty]).[if_empty]The first argument, A2:C5, specifies the full array to return; the next, B2:B5=E2, evaluates each row; and the final "No match" specifies the value to display when there are 0 matches. You cannot use it correctly unless the size of the evaluation array corresponds to the number of rows in the array to be returned. If errors are mixed into the source data during value comparison, it may result in a different error, so verify the data preconditions.
Expected Results and Reading the Output
If E2 is Stationery, two rows (P101 and P103) are expected to be returned, and entering the formula in F2 will expand the results to the required range. Changing E2 to Clothing results in 0 matching rows, which is expected to display the "No match" string specified in the third argument. Because this is not the result of verification on actual hardware, we ask readers to evaluate it in their own Excel environment. We also confirm here the official warning that omitting the third argument when there are no matching rows may result in a #CALC! error.
Concept of Spilling
When FILTER returns two rows and three columns, an area totaling six cells is required, not only in F2 where the formula was entered, but also to the right and below. This behavior, where dynamic array results expand to surrounding cells, is called spilling. Because results increase and decrease, placing another table or note directly below the formula may cause collisions during future condition changes. When existing data is present in the output destination, check the intended expansion range of the results before deleting the source data or forcibly altering the formula.
What to Check When Encountering #SPILL!
If an existing value, such as in G2, remains in the spill range of the formula in F2, a #SPILL! error may occur due to insufficient free space. This does not mean the number of rows matching the condition was zero. Before assuming there is a problem with the original table, check the expansion range for obstacles such as existing values, merged cells, or the interior of a table. On the practice sheet, entering text in G2 and re-running makes it easier to understand the cause by comparing it with the cleared state.
Difference Between Empty Strings and "No Match"
Although examples passing an empty string to the third argument are explained in official documentation, when results appear blank, it is difficult to distinguish between "truly 0 rows" and "output display is blank." Using an explicit string like "No match" in verification formulas makes it easier for users to determine what happened. However, because strings get mixed in when aggregating or passing to other formulas, ensure those values are not used numerically. It is necessary to separate the display purpose from subsequent calculation contracts.
Things to check before business use
When the product list expands, operations must be updated to extend the range from the fixed A2:C5. While using Excel tables makes it easier to manage the source data, you must understand dynamic array constraints and place spilling formulas outside the table. Furthermore, if there are extra spaces in the source data categories or if error values are mixed into columns, match judgments may differ from expectations. Check the range, value types, and output destination size before integrating this into standard operations.
When applying to work
FILTER returning nothing because there are 0 matches is different from not having a place to put the calculation results. Inspecting data conditions and display destinations separately, rather than guessing the cause based solely on appearance, is the shortcut to troubleshooting. Before reusing the formula, record the search criteria, result output destination, and source data range. It is important not to delete the source data to fix display destination errors.
When passing no-match strings to another formula
Displaying "No match" in the third argument of FILTER is useful for clearly communicating the reason for 0 results to readers. However, when passing the results to another formula, non-numeric strings may appear within the array. When you want to aggregate unit prices, whether these strings can be treated as 0 yen depends on the business process. The absence of a product is different from a product existing with a unit price of 0 yen. A design that separates text for visual presentation from numeric data required for subsequent calculations is preferable.
An experiment to identify spill destination obstacles
After setting E2 to stationery and displaying a two-row, three-column result, enter a character into the practice cell G2. Because this fills the output destination required by FILTER, a #SPILL! error appears in F2. Next, delete the character in G2 and check whether the results return without changing the data matching conditions or the formula. What is being investigated here is the output area, not the search criteria. Accidentally deleting the source data in A2:C5 will create a different cause for errors, so keep the cells you edit distinct.
When there are errors in the search criteria values
If cell errors such as #N/A are mixed into comparison column B, the errors may propagate to the evaluation of the logical array provided to include. Treating this silently as zero matches overlooks data issues. In standard operations where you receive input tables, check in advance for blank category fields, extra spaces, and cell errors. Results can only be used reliably when you have a mechanism to manage source data quality, not just understanding how to use FILTER.
Official and primary sources
Microsoft Support: FILTER function(Checked on 2026-11-04)
Microsoft Support: How to correct a #SPILL! error(Checked on 2026-11-04)
GitHub Sample
The reusable instructional version includes comments and handling for failure cases. It is stored on GitHub, but results executed and evaluated in Excel are never fabricated and published.
