WPS Spreadsheets: Look Up Values Contained in Cells with Multiple Values
Question details
The user needs to search cells that contain multiple values and return the associated result when a lookup value appears anywhere within a cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A lookup value is stored in A2, the cells to search are in B2:B5, and the corresponding results are in C2:C5.
- Observed behavior
- A standard exact-match lookup does not find the value when it is only part of a cell containing multiple values.
Confirm which column contains the lookup text and which column contains the results. Decide whether you need only the first matching result or all matching results before choosing a formula.
Use a wildcard XLOOKUP to return the first matching result
Use this method when the lookup value may appear anywhere in a cell and only the first matching result is needed.
The asterisks allow text to appear before or after the lookup value. In XLOOKUP, match mode 2 enables wildcard matching.
Select the result cell and enter =XLOOKUP("*"&A2&"*",$B$2:$B$5,$C$2:$C$5,"",2).
Press Enter, then drag the fill handle down to apply the lookup to the remaining values in column A.
If no cell in B2:B5 contains the value from A2, the formula returns a blank because the not-found argument is set to "".
Return every matching result with FILTER and TEXTJOIN
Use this method when multiple rows may contain the lookup value and their associated results should be combined into one cell.
Use wildcard VLOOKUP for compatibility with older spreadsheet versions
Use this alternative when XLOOKUP is unavailable and you only need the first matching result from the search range.
Find Partial Text Matches in WPS Spreadsheets
WPS Spreadsheets can search for a value contained anywhere in a cell by using wildcard XLOOKUP or VLOOKUP formulas. Use FILTER and TEXTJOIN when you need to combine multiple matching results.
- 1. Open your spreadsheet: Launch WPS Office, open WPS Spreadsheets, and load the workbook containing the lookup table.
- 2. Identify the data ranges: Locate the lookup value column, the cells to search, and the associated result column.
- 3. Enter a partial-match formula: Use =XLOOKUP("*"&A2&"*",$B$2:$B$5,$C$2:$C$5,"",2) in the result cell, or use the FILTER and TEXTJOIN formula for all matches.
- 4. Copy the formula: Press Enter and drag the fill handle down to apply the formula to the remaining lookup values.
Frequently Asked Questions
How do I find a value anywhere inside a cell in WPS Spreadsheets?
Use wildcard characters around the lookup value, such as =XLOOKUP("*"&A2&"*",$B$2:$B$5,$C$2:$C$5,"",2). The asterisks match any text before or after the value.
Why does an exact-match lookup fail when a cell contains several values?
Exact-match formulas require the entire cell to equal the lookup value. Use wildcard XLOOKUP or VLOOKUP so WPS Spreadsheets can find the value as part of the cell text.
How can I return all results when more than one row matches?
Use =TEXTJOIN(", ",TRUE,FILTER($C$2:$C$5,ISNUMBER(SEARCH(A2,$B$2:$B$5)),"")). This searches each cell in B2:B5 and combines the corresponding results from C2:C5.
What should I use if my WPS version does not support XLOOKUP?
Use =IFERROR(VLOOKUP("*"&A2&"*",$B$2:$C$5,2,FALSE),""). The search column must be the first column in the selected range.
