Function Problems

How to Use Excel Formulas to Return Values for Multiple Text Options

Kushani Nimanthika Oct 10, 2026 872 views

Question details

The user needs an Excel formula to assign and return different numeric values based on whether a cell contains specific text strings, including partial matches within longer text.

How to Return Specific Values for Multiple Text Options in Excel

Product: Excel
Device & OS: not provided
Scenario: Assigning specific numeric values based on text content in cells, particularly when the target words are part of a larger text string like 'Apple 1' or 'Boy 2'.
Observed behavior: The user wants to evaluate cells for multiple text criteria regardless of case sensitivity and output specific corresponding values for partial or exact text matches.

Before you start

Identify the source column containing your text strings and determine the exact numeric or text values you want to return for each specific condition.

Solution 1Recommended

Use Nested IF with ISNUMBER and SEARCH for Partial Matches

This is the most reliable method when your target text is part of a longer string and you need a case-insensitive search.

The SEARCH function finds a text string within another text string and returns its position, while ignoring case. Wrapping it in ISNUMBER converts that position into a TRUE or FALSE condition that the IF statement can evaluate.

Select the destination cell

Click on the cell where you want the returned value to appear.

Enter the formula

Type the following nested formula: =IF(ISNUMBER(SEARCH("apple",A1)),5,IF(ISNUMBER(SEARCH("boy",A1)),10,IF(ISNUMBER(SEARCH("car",A1)),15,"")))

Apply the formula

Press Enter to apply the formula. It will search cell A1 for 'apple', 'boy', or 'car' and return 5, 10, or 15 respectively.

Fill down the column

Drag the fill handle at the bottom right of the cell down to apply this formula to the rest of your dataset.

Use Nested IF with ISNUMBER and SEARCH for Partial Matches

Case Sensitivity: The SEARCH function is not case-sensitive. If you need an exact case-sensitive match (e.g., matching 'Apple' but ignoring 'apple'), replace SEARCH with the FIND function.

Solution 2

Use Nested IF for Exact Text Matches

Use this simpler formula if the cell contains only the exact target word without any additional characters, numbers, or spaces.

Advanced Spreadsheet Tool

Use WPS Spreadsheets for Complex Text Formulas

WPS Office offers a robust Spreadsheet application that fully supports advanced text processing functions like IF, ISNUMBER, and SEARCH. You can easily manage and analyze complex text conditions for free.

1. Open your dataset: Launch WPS Spreadsheets and open your .xlsx dataset containing the text strings.
2. Access the Formula tab: Click on 'Formulas' in the top ribbon menu and select 'Insert Function'.
3. Build your text evaluation: Search for the 'IF' and 'SEARCH' functions within the wizard to build your conditional evaluation without syntax errors.
4. Save with full compatibility: Apply the custom formula across your rows and save your file seamlessly as a standard Excel document.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in function wizard to help you easily build and troubleshoot nested IF statements.Lightweight application providing fast processing speeds for large text datasets.Familiar user interface making migration seamless and intuitive.

microsoft office alternative - wps office

Frequently Asked Questions

What if my target text is case-sensitive?

If you need to match the exact uppercase or lowercase formatting (e.g., matching 'Apple' but not 'apple'), replace the SEARCH function in your formula with the FIND function. FIND works exactly like SEARCH but strictly enforces case sensitivity.

How can I return a default value if none of the text options match?

In the final argument of your nested IF statement, replace the empty string ("") with your desired default numeric value or text, formatted like: =IF(..., 15, "No Match").

Can I use the IFS function instead of nested IFs for multiple conditions?

Yes, if you are using modern versions of spreadsheets, you can simplify the formula using the IFS function combined with ISNUMBER and SEARCH. This flattens the formula and avoids the need to type multiple nested IF statements.

Why is my exact match formula returning a blank cell when the text looks identical?

Your source cell likely contains hidden characters, such as trailing spaces (e.g., 'apple ' instead of 'apple'). You can fix this by either wrapping the cell reference in the TRIM function or by switching to the partial match formula using ISNUMBER and SEARCH.

Excel & Data Problems