Excel Search Bar Formula

Objective

To create a search bar functionality in Excel that enables users to filter or find specific entries in a dataset based on user input.

Formula Overview

The formula utilizes the FILTER function (available in Excel 365 and Excel 2021) or a combination of SEARCH and IFERROR functions (for earlier versions) to achieve a search capability.

1. Using the FILTER Function (Excel 365/2021)

Syntax

=FILTER(range, ISNUMBER(SEARCH("search_term", range)), "No results found")

Components

• range: The full dataset or column you want to search through.
• search_term: The term you are searching for, typically linked to a cell where the user inputs their search query.
• ISNUMBER: This checks if the SEARCH function returns a number, indicating a match.
• SEARCH: This function finds the position of the search term, returning a number if found.
• "No results found": This text is displayed if no matches are found.

Example

Assuming you have a list of names in column A (A2:A10) and the search term is entered in cell B1:

=FILTER(A2:A10, ISNUMBER(SEARCH(B1, A2:A10)), "No results found")

2. Using SEARCH with IFERROR (For Earlier Versions)

Syntax

=IFERROR(INDEX(range, SMALL(IF(ISNUMBER(SEARCH("search_term", range)), ROW(range)-ROW(INDEX(range,1,1))+1), ROW(1:1))), "")

Components

• INDEX: Returns the value of a cell in a specified row and column.
• SMALL: Returns the k-th smallest value in a data set, which can be used to extract matching positions.
• ROW(range): Generates an array of row numbers which is adjusted to match the dataset.
• IFERROR: This suppresses error messages when no matches are found.

Example

Using the same example dataset in column A and search term in B1:

=IFERROR(INDEX(A2:A10, SMALL(IF(ISNUMBER(SEARCH(B1, A2:A10)), ROW(A2:A10)-ROW(A2)+1), ROW(1:1))), "")

Note: This formula must be entered as an array formula (Ctrl + Shift + Enter), resulting in curly braces around the formula.

Practical Illustration

Assuming the following sample data in cells A2:A10:

Implementation

1. Input: In cell B1, the user types a.
2. Using the FILTER Formula:
   • In cell C1, enter: =FILTER(A2:A10, ISNUMBER(SEARCH(B1, A2:A10)), "No results found")
3. Outcome: C1 displays results: Alice, Charlie, David.

Outcome With IFERROR + INDEX

1. Input: Keeping the same search term in B1.
2. In cell C1, input: =IFERROR(INDEX(A2:A10, SMALL(IF(ISNUMBER(SEARCH(B1, A2:A10)), ROW(A2:A10)-ROW(A2)+1), ROW(1:1))), "")
3. Drag down to C2, C3, etc.
4. Outcome: C1 displays Alice; C2 displays Charlie; subsequent cells display other matches.

Conclusion

These formulas provide effective mechanisms to implement a search bar-like functionality in Excel. The FILTER function is more straightforward and efficient for newer versions of Excel, while the SEARCH with IFERROR method supports older versions. Users can adapt these formulas to various datasets and search criteria.

For further mastering of Excel and data analysis techniques, consider exploring courses on the Enterprise DNA Platform.