Function Problems

Use an Array in a Nested IF Formula to Find Multiple Text Values in Excel

Maira Mehtab Sep 21, 2026 868 views

Question details

The user wants to check multiple cells for specific text strings and return a distinct value if any of the criteria are met, without resorting to lengthy nested IF statements.

Product: Excel
Device & OS: not provided
Scenario: Evaluating multiple cells (e.g., Z8 and AA8) to see if they contain any specific keywords from a predefined list (e.g., "Transfer" or "3M") to output a categorized result.
Observed behavior: Instead of chaining multiple IF functions, the user seeks an efficient array-based formula to process multiple search terms and return "Investment".

Before you start

Verify that your spreadsheet's regional settings support using semicolons or commas as array separators, as this dictates the exact syntax of your array constant.

Solution 1Recommended

Use an Array Constant with OR, ISNUMBER, and SEARCH Functions

Combine an array constant inside the SEARCH function to look for multiple terms simultaneously, streamlining your formula and eliminating nested IF statements.

By placing an array of text values into the SEARCH function, Excel looks for all listed strings at once. The ISNUMBER function converts the successful matches into TRUE, and the OR function ensures that if at least one match is TRUE, the IF function returns your desired outcome.

Select the Destination Cell

Click on the cell where you want the evaluated result to be displayed (for example, cell AC8).

Enter the Array Formula

In the formula bar, type the following formula: =IF(OR(ISNUMBER(SEARCH({"Transfer";"3M"},Z8:AA8))),"Investment","")

Verify Array Separators

Ensure you are using the correct separator for your array constant. Depending on your system settings, you may need to use a semicolon ({"Transfer";"3M"}) or a comma ({"Transfer","3M"}).

Apply and Test the Formula

Press Enter to execute the formula. The cell will now display "Investment" if either "Transfer" or "3M" is detected in the specified cells (Z8 or AA8).

Case Insensitivity: The SEARCH function is not case-sensitive. If you require a strict case-sensitive match, replace SEARCH with the FIND function in your formula.

Simplify Complex Data Processing

Process Array Formulas Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array constants and logical functions, making it incredibly easy to search for multiple text values without building cumbersome nested IF formulas.

1. Open Your Document in WPS Spreadsheet: Launch WPS Office and open the workbook containing the data you want to analyze.
2. Input the Search Formula: Select the target cell, type the combined IF and SEARCH array formula, and customize the search terms inside the curly brackets.
3. Drag to Fill: Press Enter to validate the formula, then use the fill handle at the bottom right of the cell to drag the formula down to other rows.

Seamlessly compatible with Microsoft Excel formulas and array constantsFast processing engine for handling large data ranges and complex logicUser-friendly formula builder with built-in syntax tipsFree and lightweight Microsoft Office alternative

microsoft office alternative - wps office

Frequently Asked Questions

Can I add more than two text values to search for?

Yes, you can include as many text strings as you need within the curly brackets. Just separate each new term with a semicolon or comma, like this: {"Transfer";"3M";"Deposit";"Withdrawal"}.

Why is my array formula returning a #VALUE! error?

A #VALUE! error often occurs if the array syntax is incorrect or if the separator used in the array constant (comma vs. semicolon) does not match your operating system's regional settings.

What does the empty string at the end of the formula do?

The empty string ("") at the end of the IF function acts as the 'value_if_false' argument. It tells the formula to leave the cell blank if none of the specified text values are found.

Excel & Data Problems