How to Use an Array Formula to Filter Data by Keyword in Excel

In this article, we will learn how to use an array formula in Excel to enter an array of values based on a keyword in another cell. This can be useful when you want to create a dynamic drop-down list or categorize text cells based on their content.

What is an array formula?

An array formula is a special type of formula that can perform multiple calculations and return multiple results at once. An array formula can also process an array of values as input or output, rather than a single value. An array is a collection of items that can be stored in a single row, column, or multiple rows and columns.

To enter an array formula, you need to press Ctrl + Shift + Enter instead of just Enter. This will enclose the formula in curly braces { } to indicate that it is an array formula. You cannot edit or delete the braces manually, you need to use Ctrl + Shift + Enter again to modify or delete the array formula.

How to enter an array based on cell keyword

To enter an array based on cell keyword, we can use a combination of the following functions:

• The SEARCH function, which returns the position of a text string within another text string, or an error value if not found. The SEARCH function is case-insensitive and allows wildcards.
• The ISNUMBER function, which returns TRUE if a value is a number, and FALSE otherwise.
• The MATCH function, which returns the relative position of an item in an array that matches a specified value, or an error value if not found. The MATCH function can perform an exact match or an approximate match based on the specified match type argument.
• The INDEX function, which returns the value at a given position in an array or a range of cells.

The basic syntax of the formula is:

=INDEX(array,MATCH(TRUE,ISNUMBER(SEARCH(keyword,text)),0))

Where:

• array is the array of values that you want to enter based on the keyword.
• keyword is the cell that contains the keyword to look for in the text.
• text is the cell or range of cells that contain the text to search for the keyword.

The formula works as follows:

• The SEARCH function returns an array of numbers or error values, depending on whether the keyword is found in each text cell or not.
• The ISNUMBER function converts the array of numbers or error values into an array of TRUE or FALSE values, respectively.
• The MATCH function returns the position of the first TRUE value in the array, which corresponds to the first text cell that contains the keyword.
• The INDEX function returns the value in the array at the position returned by the MATCH function.

Example: Create a dynamic drop-down list based on cell keyword

Suppose we have a list of products in column A, and we want to create a drop-down list in cell B1 that shows only the products that contain a keyword entered in cell C1. For example, if we enter “pen” in cell C1, we want the drop-down list in cell B1 to show only the products that contain “pen” in their names.

To do this, we can use the following steps:

• Create a named range for the list of products. Select the range A2:A11, and go to the Formulas tab, click Define Name, and enter a name, such as “products”.
• Create a named range for the keyword. Select cell C1, and go to the Formulas tab, click Define Name, and enter a name, such as “keyword”.
• Enter the array formula in cell B1. Type =INDEX(products,MATCH(TRUE,ISNUMBER(SEARCH(keyword,products)),0)) and press Ctrl + Shift + Enter. This will enter the first product that contains the keyword in cell B1.
• Create a data validation list based on the array formula. Select cell B1, and go to the Data tab, click Data Validation, and choose List from the Allow drop-down list. In the Source box, enter the same array formula as in step 3, and click OK. This will create a drop-down list in cell B1 that shows only the products that contain the keyword in cell C1.

The following table shows the result of the example:

You can change the keyword in cell C1 and see how the drop-down list in cell B1 changes accordingly.

Other approaches

There are other ways to enter an array based on cell keyword, such as using the FILTER function (available in Excel 365 and Excel 2019) or using a helper column with a regular formula. However, the array formula approach has some advantages, such as:

• It does not require any extra columns or cells to store intermediate results.
• It can handle multiple keywords by using an array constant, such as {“pen”,“pencil”} instead of a single cell reference.
• It can handle partial matches by using wildcards, such as “pen” instead of “pen”.