Function Problems

How to Create an Excel 2019 Search Bar Without the FILTER Function

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to build a searchable results area in Excel 2019, which lacks support for the newer dynamic-array FILTER function.

Product: Microsoft Excel 2019
Device & OS: not provided
Scenario: Creating a dynamic search box that retrieves and displays matching records from a dataset in an older Excel version.
Observed behavior: Excel 2019 returns an error or does not recognize the FILTER function, requiring traditional lookup formulas or VBA to replicate the behavior.

Before you start

Before setting up your search formulas, ensure your source dataset is organized into clear columns and dedicate a specific cell (such as E1) to serve as your search input box.

Solution 1Recommended

Use Helper Columns with INDEX and SMALL Functions

This method uses traditional lookup formulas supported by Excel 2019 to sequentially extract records matching your search term.

Since Excel 2019 does not support dynamic arrays, you must calculate matching row numbers in a helper column first. This makes the final lookup formula much more efficient.

Set up the search cell

Designate a cell, for example E1, where you will type the search term you want to look for.

Create a helper column for row numbers

Next to your data table, create a helper column. Use the SEARCH function to see if the row contains the term from E1, and wrap it in an IF and IFERROR statement to assign a sequential row number to matches.

Extract matching records using INDEX and SMALL

In your results area, write an INDEX formula that references your original data table. Use the SMALL function to pull the 1st, 2nd, 3rd, etc., matching row numbers from your helper column.

Handle empty results and copy down

Wrap your INDEX formula in IFERROR(" ") to display blank cells when there are no more matches, then drag the formula down across your results area.

Performance Tip: Using a dedicated helper column rather than calculating everything in a single array formula prevents Excel from slowing down when processing large datasets.

Solution 2

Use a VBA Macro to Filter Records

If you prefer not to use complex formulas and helper columns, a VBA script can dynamically filter your data when a search box value changes.

Advanced Data Management

Create Powerful Search Bars in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, SEARCH, and SMALL, allowing you to build dynamic search bars seamlessly without needing the newest Excel versions.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the data.
2. Assign a search cell: Pick a cell to act as your search bar and enter a test keyword.
3. Build the helper column: Use the SEARCH function in an empty column to flag matching records.
4. Retrieve the data: Use INDEX and SMALL functions in a new table area to extract and display the flagged results sequentially.

100% compatible with Microsoft Excel formulas and .xlsx filesFast data processing for complex nested formulasLightweight application that runs smoothly on older devicesFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does the FILTER function return a #NAME? error in Excel 2019?

The #NAME? error occurs because the FILTER function is a dynamic array function introduced in Microsoft 365 and Excel 2021. Excel 2019 does not recognize this function name in its built-in formula library.

Can I use Advanced Filter instead of complex formulas?

Yes, the Advanced Filter tool under the Data tab can extract records to a new location based on complex criteria. However, unlike formulas, it does not update automatically when you change the search term; you must run the filter again manually.

Does Conditional Formatting work as a search bar?

Yes, you can use Conditional Formatting to highlight rows that match your search term. While it won't hide unmatched rows like a true filter, it visually emphasizes the matching data. You can set this up using a rule like =SEARCH($E$1, $A2) applied to your data range.

Excel & Data Problems