How to Use SEARCH Function with Multiple Values in Excel

Using the SEARCH function with multiple values in Excel involves looking for multiple text strings within a single cell. To achieve this, you can use a combination of the SEARCH function, ISNUMBER function, and SUMPRODUCT function.

Here's a step-by-step guide on how to use the SEARCH function with multiple values in Excel:

1. Prepare your data: Make sure your data is well-organized in columns and rows. For this example, let's assume you have a list of product names in column A (from A2 to A10), and you want to search for the products containing the words "Red" or "Blue" in their names.
2. Use the SEARCH function with multiple values: In an empty cell, type the following formula:

=SUMPRODUCT(--(ISNUMBER(SEARCH({"Red","Blue"},A2))))

3. Press Enter: This formula will return the number of occurrences of the two search values ("Red" and "Blue") in the cell A2.
4. Copy the formula: If you want to apply the same formula to other cells in the column, click on the cell with the formula, then click on the bottom-right corner of the cell and drag it down to fill the other cells.
5. Interpret the results: The formula will return a number that represents the number of occurrences of the search values in each cell. If the result is 0, it means none of the search values were found in the cell. If the result is 1 or greater, it means at least one of the search values was found in the cell.

Example:

In this example, column B contains the results of the SEARCH function with multiple values. The formula in B2 is =SUMPRODUCT(--(ISNUMBER(SEARCH({"Red","Blue"},A2)))), and it has been dragged down to fill the other cells in column B.