Formula Errors

How to Fix FILTER with SEARCH and ISNUMBER #VALUE! Error

Maira Mehtab Sep 24, 2026 870 views

Question details

The user is attempting to build a dynamic search bar using the FILTER, SEARCH, and ISNUMBER functions, but the combined formula results in a #VALUE! error.

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering a dataset dynamically based on partial text matches entered into a designated search bar cell.
Observed behavior: While SEARCH and ISNUMBER function properly on their own, nesting them inside the FILTER function's criteria argument produces a #VALUE! error instead of returning the filtered array.

Before you start

Ensure your spreadsheet software fully supports dynamic array formulas, and verify that the source dataset does not already contain existing #VALUE! or #REF! errors that could break the calculation.

Solution 1Recommended

Wrap the SEARCH Expression with IFERROR

Use the IFERROR function to catch unhandled errors from the SEARCH function before they propagate through the array and cause the FILTER function to fail.

In some spreadsheet environments or specific data conditions, the SEARCH function returning a #VALUE! error (when a substring is not found) can break the array evaluation before ISNUMBER can convert it to FALSE. Wrapping SEARCH in IFERROR neutralizes these errors.

Select the formula cell

Click on the cell containing your existing FILTER formula that is currently returning the #VALUE! error.

Modify the SEARCH function

Locate the SEARCH portion of your formula. Wrap it with IFERROR to return a 0 if an error occurs. For example, change SEARCH(D2, A2:A100) to IFERROR(SEARCH(D2, A2:A100), 0).

Update the full formula

Your complete formula should now look similar to: =FILTER(A2:C100, ISNUMBER(IFERROR(SEARCH(D2, A2:A100), 0))). Press Enter to apply the changes and spill the correct results.

Formula Tip: If you want to handle empty search results gracefully, don't forget to use the third argument of the FILTER function, for example: =FILTER(..., "No results found").

Solution 2

Verify and Match Array Range Dimensions

Ensure the range used in the FILTER function exactly matches the range size evaluated by the SEARCH function, as mismatched dimensions always return a #VALUE! error.

Create Dynamic Search Bars Easily with WPS Spreadsheets

Build robust, error-free search formulas using WPS Office. Its advanced spreadsheet engine supports all modern dynamic arrays, ensuring your FILTER, SEARCH, and ISNUMBER combinations work seamlessly without unexpected #VALUE! errors.

1. Open your dataset: Launch WPS Spreadsheets and open the workbook containing the data you want to filter.
2. Select the target cell: Click the top-left cell where you want your dynamic search results to appear.
3. Enter the formula: Type the formula: =FILTER(A2:C20, ISNUMBER(SEARCH(E2, A2:A20)), "Not found") and ensure E2 points to your search bar.
4. View the results: Press Enter. The results will dynamically spill into the neighboring cells and update instantly as you type in the search bar.

Fully compatible with Microsoft Excel formulas and array logicNative support for modern dynamic arrays like FILTER, UNIQUE, and SORTFree, lightweight, and fast spreadsheet processing

microsoft office alternative - wps office

Frequently Asked Questions

Why does ISNUMBER(SEARCH()) return a #VALUE! error instead of TRUE/FALSE?

Normally, ISNUMBER converts the error from SEARCH into FALSE. However, if the underlying cells being searched already contain an error (like #DIV/0! or #REF!), or if the array dimensions inside a dynamic formula like FILTER do not match exactly, a #VALUE! error will force its way through the entire formula.

Does WPS Office support the FILTER function?

Yes, the latest versions of WPS Office fully support the FILTER function along with other dynamic array formulas, offering complete compatibility with modern spreadsheet standards.

How do I prevent the #CALC! or #VALUE! error when nothing is found?

To prevent an error when there are no matches, utilize the third optional argument of the FILTER function. Simply append a custom message at the end of the formula, like this: =FILTER(A2:B10, criteria, "No matches found").

Excel & Data Problems