Excel Search Bar Formula

Objective

To create a search functionality in Excel that allows users to find specific text within a range of cells. This can be particularly useful for filtering data in a table or a list.

Concept

This functionality can be implemented using the SEARCH, IF, and FILTER functions in Excel, depending on the Excel version:

For Excel 365 Users

The FILTER function can be combined with SEARCH to create a dynamic search bar.

For Excel 2019 and Older Users

The SEARCH function can be used in conjunction with other functions but may not allow dynamic filtering.

Formula Explanation

1. For Excel 365 Users

Formula:

=FILTER(A2:A100, ISNUMBER(SEARCH(D1, A2:A100)))

Explanation:

• FILTER: This function filters a range based on a condition.
• A2:A100: This is the range containing the text data to be searched.
• D1: This cell contains the search term entered by the user.
• SEARCH: This function returns the position of the search term within each cell. If found, it returns a number.
• ISNUMBER: This checks if the result from SEARCH is a numeric value (meaning the search term was found).

2. For Excel 2019 and Older Users

Formula:

=IFERROR(INDEX(A$2:A$100, SMALL(IF(ISNUMBER(SEARCH(D$1, A$2:A$100)), ROW(A$2:A$100)-ROW(A$2)+1), ROW(1:1))), "")

Explanation:

• IFERROR: This handles errors gracefully by returning a blank if an error occurs.
• INDEX: Extracts data from a specified row in the range.
• SMALL: Retrieves the kth smallest value from an array, which helps in collecting matching row positions.
• SEARCH: As explained previously, checks for the term in the range.
• ROW(A$2:A$100)-ROW(A$2)+1: Calculates relative row numbers for filtering.
• ROW(1:1): This part allows the formula to return multiple results when dragged down.

Practical Illustration

Scenario:

Assume you have a list of products in column A (A2:A100), and you want to search for products using a search term in cell D1.

Implementation Steps:

1. Enter products in cells A2 to A100.
2. Enter the text you want to search for in cell D1.
3. For Excel 365, enter the FILTER formula in cell E2. For Excel 2019 or older, enter the IFERROR + INDEX formula in cell E2, then drag down.

Example:

• Column A (Products):

  • Apple
  • Banana
  • Orange
  • Grapes

• Cell D1 (Search Term): Banana

• Results:

  • If using the FILTER formula, cell E2 will show "Banana".
  • If using the IFERROR + INDEX formula, populate as you drag down.

Conclusion

With this approach, users can efficiently search for terms within a specified range in Excel. The method used can vary based on the Excel version available, ensuring that all users can benefit from a search bar functionality in their spreadsheets. For further mastery of such techniques, consider exploring the courses available on the Enterprise DNA Platform.