How to Use XLOOKUP with Multiple Semicolon-Separated Values in WPS Spreadsheets
Question details
Split semicolon-separated lookup keys, use XLOOKUP on each key, and combine the returned values into one cell with TEXTJOIN.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a cell containing multiple semicolon-separated keys, such as issue keys or promo codes, and needs to look up each key in a table and show all matching results in one cell.
- Observed behavior
- The goal is to produce one comma-separated text string containing the lookup result for every semicolon-separated key in the source cell.
Make sure your WPS Office version supports dynamic array functions such as TEXTSPLIT and XLOOKUP. If TEXTSPLIT is not available, use the FILTERXML alternative shown below.
Use TEXTSPLIT inside XLOOKUP and combine the results with TEXTJOIN
Best when your WPS Office version supports TEXTSPLIT and dynamic arrays; it splits the cell once and looks up every value in a single formula.
This method uses TEXTSPLIT to convert a semicolon-separated cell into an array of lookup values. XLOOKUP then returns the corresponding value for each item, and TEXTJOIN combines all returned values into one comma-separated string.
Open your workbook in WPS Office Spreadsheets and click the cell where you want the combined lookup results to appear.
Type =TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT(A2,";"),$A$2:$A$89,$B$2:$B$89,"Not found")) and replace A2 with the cell that contains your semicolon-separated keys.
Change $A$2:$A$89 to the column containing the keys to search and $B$2:$B$89 to the column containing the values to return.
Press Enter to calculate the formula. Confirm that each semicolon-separated key returns the correct value and missing keys appear as "Not found".
Use FILTERXML as a compatible alternative when TEXTSPLIT is unavailable
Use this method in versions where TEXTSPLIT is not supported, because FILTERXML can split semicolon-separated text into an array for XLOOKUP.
Combine XLOOKUP, TEXTSPLIT, and TEXTJOIN for Multi-Value Lookups in WPS Office
WPS Office Spreadsheets lets you split semicolon-separated values, look up each value with XLOOKUP, and combine the returned results into one cell using TEXTJOIN.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx workbook that contains the semicolon-separated values and the lookup table.
- 2. Select the result cell: Click the cell where you want the combined lookup results to appear.
- 3. Enter the XLOOKUP, TEXTSPLIT, and TEXTJOIN formula: Type =TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT(A2,";"),$A$2:$A$89,$B$2:$B$89,"Not found")) and replace A2 and the ranges with your actual data.
- 4. Press Enter and verify the result: Press Enter to calculate the formula. Check that every semicolon-separated key returns the correct value and missing keys show "Not found".
Frequently Asked Questions
What formula splits semicolon-separated values and looks up each one?
Use =TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT(A2,";"),lookup_range,return_range,"Not found")). TEXTSPLIT creates an array, XLOOKUP returns a value for each item, and TEXTJOIN combines them into one cell.
What can I do if TEXTSPLIT is not available in my WPS Office version?
Update WPS Office to a version that supports dynamic array functions, or use the FILTERXML formula that replaces TEXTSPLIT with FILTERXML("<t><s>"&SUBSTITUTE(A2,";","</s><s>")&"</s></t>","//s").
Can I use a different delimiter instead of a semicolon?
Yes. Replace the ";" delimiter in TEXTSPLIT or SUBSTITUTE with your delimiter, such as "," or "|", and adjust the formula accordingly.
How do I display a custom message when a lookup key is not found?
Replace the "Not found" argument in XLOOKUP with your preferred text, such as "Missing" or "No match".
Does this multi-value lookup formula work with Excel files in WPS Office?
Yes. WPS Office Spreadsheets opens .xlsx workbooks and supports XLOOKUP and TEXTJOIN. For TEXTSPLIT, make sure your WPS Office version supports dynamic arrays.
