How to Look Up a Number Inside Comma-Separated Values in WPS Office Excel
Question details
The user needs to find which comma-separated number list contains a specific lookup number and return the associated name from an adjacent column in WPS Office Excel.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A lookup value in G2 must be matched against cells containing multiple numbers separated by commas, and the corresponding name must be returned.
- Observed behavior
- A standard exact VLOOKUP cannot match the number because it compares the entire cell value, not individual list items.
Confirm that the comma-separated number lists and the associated names are in adjacent columns or named ranges. Make sure the lookup number in G2 uses the same number format and does not include extra spaces.
Use INDEX and MATCH with a wildcard to search comma-separated lists
Use this basic formula when you want a quick wildcard lookup and the stored lists are consistent.
This formula uses MATCH with wildcard characters to treat the comma-separated cell text as the search range. It returns the associated name from Name_Column, or an empty string when no match is found.
Click the cell where you want the matched name to appear.
Type =IFERROR(INDEX(Name_Column,MATCH("*"&G2&"*",Number_Column,0)),"") in the formula bar.
Press Enter to confirm. Drag the fill handle down to copy the formula for other lookup numbers.
Use comma-padded MATCH to avoid partial-number false matches
Use this formula when your lists contain numbers that are also substrings of other numbers, such as 12 and 123.
Normalize comma-separated lists into one number per row
Use this approach for long-term reporting and to allow standard lookup functions without wildcard workarounds.
Use WPS Office Excel to Look Up Numbers in Comma-Separated Lists
Use WPS Office Excel's INDEX and MATCH wildcard formulas to search comma-separated lists directly in your workbook, and switch to WPS for free spreadsheet editing.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office, click Open, and select the Excel workbook that contains the comma-separated number lists.
- 2. Select the output cell: Click the cell where you want the associated name to appear, such as the cell next to the lookup number G2.
- 3. Enter the lookup formula: In the formula bar, type =IFERROR(INDEX(Name_Column,MATCH("*,"&G2&",*",","&Number_Column&",",0)),"").
- 4. Apply the formula to remaining lookup values: Press Enter, then drag the fill handle down to apply the formula to the other numbers you need to look up.
Frequently Asked Questions
Why does VLOOKUP fail when the number is inside a comma-separated cell?
Exact VLOOKUP compares the whole cell value. If the cell contains multiple comma-separated numbers, it does not match a single number. Use an INDEX MATCH formula with wildcards or comma-padded MATCH to search inside the list.
How do I prevent 12 from matching 123 in a comma-separated list?
Add commas around both the stored list and the lookup value. Use =IFERROR(INDEX(Name_Column,MATCH("*,"&G2&",*",","&Number_Column&",",0)),"") so only complete comma-delimited numbers are matched.
Can I use these lookup formulas in WPS Office Excel and Microsoft Excel?
Yes. The INDEX MATCH wildcard and comma-padded MATCH formulas work in WPS Office Excel and are compatible with Microsoft Excel .xlsx workbooks.
What should I do if the comma-separated values have spaces after commas?
Remove or standardize spaces in the list column first, or use TRIM/CLEAN to clean the entries. The comma-padded formula expects commas directly between numbers.
Is it better to split comma-separated values into separate rows?
For long-term reporting it is generally better. A normalized table with one number per row lets you use exact VLOOKUP or XLOOKUP without wildcard workarounds.
