Match Part of a Cell and Return Yes or No in WPS Spreadsheets
Question details
Find whether a value from one cell appears anywhere within cells in a column on another sheet, then return Y or N.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to compare each value in a source column against text contained in a lookup column on another worksheet.
- Observed behavior
- The desired result is Y when the source value is found as part of any cell in the lookup range, and N when no matching text is found.
Identify the source cell or table column that contains the value to search for and the target column on the other worksheet. Make sure the lookup range contains text values and replace the example sheet and column references with your own.
Use COUNTIF with wildcards to find text within another cell
Use this formula when the lookup value may be only part of a longer text string in the target column.
Asterisks (*) are wildcard characters in COUNTIF. Placing one before and after the lookup value searches for that value anywhere in each cell of the target range.
Click the first cell where you want to display Y or N, such as C2 on your source worksheet.
Type =IF(COUNTIF(Lookup!B:B,"*"&A2&"*")>0,"Y","N"), replacing Lookup!B:B with the column to search and A2 with the cell containing the value to find.
Press Enter, then drag the fill handle down the column or use Home > Fill > Down to apply the formula to the remaining rows.
Use COUNTIF without wildcards for an exact cell match
Use this version when the source value must exactly equal a cell in the target range.
Use a single-cell or structured reference in a table formula
Use a row-specific reference to avoid array or spill-style results when entering the formula inside a table.
Check Partial Text Matches with COUNTIF in WPS Spreadsheets
WPS Spreadsheets lets you use COUNTIF, IF, wildcard characters, and cross-sheet references to identify text contained within another cell and show an easy-to-read Y or N result.
- 1. Open the workbook in WPS Spreadsheets: Open your workbook and select the worksheet where the Y or N result should appear.
- 2. Choose the first result cell: Click the first blank cell in the result column, for example C2.
- 3. Add the wildcard COUNTIF formula: Enter =IF(COUNTIF(Lookup!B:B,"*"&A2&"*")>0,"Y","N") and update the sheet name, lookup range, and source cell as needed.
- 4. Fill the formula through the list: Press Enter and drag the fill handle down to evaluate every row.
Frequently Asked Questions
What do the asterisks mean in a COUNTIF criterion?
In COUNTIF, * represents any number of characters. The criterion "*"&A2&"*" searches for the value in A2 at the beginning, middle, or end of a cell.
How can I return Yes and No instead of Y and N?
Change the last two arguments of IF. For example, use =IF(COUNTIF(Lookup!B:B,"*"&A2&"*")>0,"Yes","No").
Why does my formula return N even though the text looks the same?
Check that the formula points to the correct worksheet and column, and look for extra spaces or different characters in the source value. You can test the source cell with =TRIM(A2) if unwanted spaces are present.
Can I use this formula inside a spreadsheet table?
Yes. Use a current-row structured reference such as [@ColumnName] in place of A2: =IF(COUNTIF(Lookup!B:B,"*"&[@ColumnName]&"*")>0,"Y","N").
How do I check for an exact match rather than text contained within a cell?
Remove the wildcard characters and use =IF(COUNTIF(Lookup!B:B,A2)>0,"Y","N"). This returns Y only when a cell in the lookup range exactly matches A2.
