Function Problems

Excel Formula to Identify Matched Sets with Partial Text

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula to return 'YES' when comparing cells to check if they contain specific names or partial text strings, such as matching 'Jones' by only searching for 'JO'.

Product: Excel
Device & OS: not provided
Scenario: Comparing and cross-referencing data across multiple cells to identify matched sets using incomplete or partial text strings.
Observed behavior: The user attempted to use asterisks as wildcards to perform a partial text match in their formula, but the formula did not return the expected results.

Before you start

Ensure your dataset is organized in clear columns and check that your target cells do not contain unexpected leading or trailing spaces that could interfere with text matching functions.

Solution 1Recommended

Use the SEARCH and ISNUMBER Functions

Combining SEARCH with ISNUMBER inside an IF statement is the most reliable way to identify partial text strings without relying on wildcard syntax.

The SEARCH function looks for a text string within a cell and returns its starting position as a number. If the text is not found, it returns an error. By wrapping it in ISNUMBER, we convert the result into a simple TRUE or FALSE condition that the IF function can evaluate.

Select the target cell

Click on the cell where you want the 'YES' or 'NO' result to appear.

Enter the nested formula

Type the formula `=IF(OR(ISNUMBER(SEARCH("JO", A2)), ISNUMBER(SEARCH("Smith", A2))), "YES", "NO")` into the formula bar, replacing A2 with the cell you are testing.

Apply and drag

Press Enter to see the result. You can then click and drag the fill handle at the bottom-right corner of the cell to apply this formula to the rest of your dataset.

Case Insensitivity: The SEARCH function is not case-sensitive, meaning searching for 'JO' will successfully match 'Jones', 'jones', and 'JONES'.

Solution 2

Use the COUNTIF Function with Asterisk Wildcards

For users who prefer using wildcards, the COUNTIF function supports asterisk (*) symbols to find partial text matches.

Easily Match Partial Text with WPS Spreadsheet Formulas

WPS Office Spreadsheet fully supports advanced text functions, including SEARCH, COUNTIF, and wildcards. It allows you to seamlessly process complex datasets and identify partial text matches with exactly the same formula syntax used in Microsoft Excel.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data sets you need to analyze.
2. Select the formula cell: Click the blank cell where you want the matched result to be displayed.
3. Insert the partial text formula: Type your `=IF(COUNTIF(A2, "*JO*"), "YES", "NO")` formula in the formula bar and press Enter.
4. Fill the remaining cells: Hover over the bottom-right corner of the cell until the crosshair appears, then drag down to fill the formula for the rest of your data set.

100% compatible with Microsoft Excel formulas and wildcardsBuilt-in formula evaluation tool to easily troubleshoot complex nested IF functionsSupports advanced text processing functions like ISNUMBER, SEARCH, and MATCHFree, lightweight, and fast alternative for heavy spreadsheet data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why are my asterisk wildcards not working in a basic IF statement?

The standard IF function with an equals sign (e.g., =IF(A1="*text*", ...)) interprets the asterisk literally as a character, not as a wildcard. To use wildcards for partial matches, you must use functions that support them, such as COUNTIF, SEARCH, or MATCH.

Does the SEARCH function distinguish between uppercase and lowercase text?

No, the SEARCH function is case-insensitive. If your partial text match needs to be strictly case-sensitive, you should use the FIND function instead, applying the exact same ISNUMBER(FIND(...)) structure.

How do I search for multiple partial text strings across two different cells?

You can combine multiple conditions using the AND or OR functions. For example, to check if A1 contains 'Smith' AND B1 contains 'JO', you can write: `=IF(AND(ISNUMBER(SEARCH("Smith", A1)), ISNUMBER(SEARCH("JO", B1))), "YES", "NO")`.

Excel & Data Problems