How to Use an Excel Partial-Match Formula to Return a Value or Blank in WPS Office
Question details
The user needs a spreadsheet formula that checks K28:K400 for partial text matching and returns the value from D5 in E5 when a match exists, or blank when no match exists.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is working with a WPS/Excel-compatible spreadsheet and wants a reusable formula for partial text matching in a large lookup range.
- Observed behavior
- A COUNTIF-based wildcard formula can handle partial matches, including spaces and slash characters, and returns a blank when no match is found.
Open your WPS Office Spreadsheet workbook and confirm that D5 contains the lookup text and K28:K400 is the range to search. If D5 can be blank, plan to use a blank-check variant so the formula does not return an empty-looking result for an empty lookup cell.
Use COUNTIF with Wildcards to Return a Matching Value or Blank
Use this when D5 is not blank and you want E5 to display the value from D5 only if a partial match is found in K28:K400.
The COUNTIF function treats the text from D5 as a pattern when it is surrounded by asterisks. Spaces and slash characters are treated as ordinary text, so no special escaping is needed for them.
Click cell E5 in your WPS Office Spreadsheet where you want the result to appear.
Type =IF(COUNTIF($K$28:$K$400,"*"&$D5&"*"),$D5,"") into the formula bar.
Press Enter. If D5 is found inside any cell in K28:K400, E5 displays the D5 value; otherwise it remains blank.
Add a Blank Check to Avoid Reading a Blank Lookup Cell
Use this variant when D5 can be empty and you want E5 to stay blank instead of matching blank cells in the lookup range.
Use WPS Office Spreadsheet to Build Excel-Compatible Partial-Match Formulas
You can complete this task directly in WPS Office Spreadsheet using the same COUNTIF wildcard formula. Open the workbook, enter the formula in E5, and WPS Office preserves Excel-compatible calculation behavior.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheet and open the Excel workbook that contains D5 and K28:K400.
- 2. Select the result cell: Click E5, or the first cell where you need the partial-match result.
- 3. Enter the COUNTIF formula: Type =IF(COUNTIF($K$28:$K$400,"*"&$D5&"*"),$D5,"") and press Enter.
- 4. Verify and save: Check that E5 returns the value from D5 or blank, then save the file as .xlsx to keep Excel compatibility.
Frequently Asked Questions
Why does the formula use asterisks around the lookup cell?
Asterisks are Excel wildcards. In COUNTIF, "*"&$D5&"*" matches any cell that contains the text from D5 anywhere, not just cells that exactly equal D5.
Will spaces and slash characters in the lookup range break the formula?
No. Spaces and slash characters are treated as ordinary text inside COUNTIF, so no special escaping is required.
What happens if D5 is empty?
The basic formula can match blank cells and may return an empty-looking result. Use the blank-safe variant =IF($D5="","",IF(COUNTIF($K$28:$K$400,"*"&$D5&"*"),$D5,"")) to keep E5 blank when D5 is empty.
Can I copy this formula across rows or columns?
Yes. The range $K$28:$K$400 is absolute, so it stays fixed. The lookup reference $D5 has an absolute column and relative row, so copying down changes it to D6, D7, and so on.
Does this formula work in WPS Office as well as Excel mobile?
Yes. WPS Office Spreadsheet supports COUNTIF wildcards and is compatible with Excel formulas and .xlsx files.
