Use XLOOKUP to Find Comma-Separated Values in Excel
Question details
Find a lookup value within comma-separated entries and return the corresponding result from another column.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A lookup value is compared with comma-separated values in the Invoice sheet, and a matching result is returned from column C.
- Observed behavior
- A wildcard lookup can match an unintended partial value or interpret wildcard characters in the lookup text.
Confirm that the lookup value is in E3, the comma-separated entries are in Invoice!A1:A3000, and the return values are in Invoice!C1:C3000. For the exact-token formula, remove spaces immediately before or after commas in the source entries.
Match complete comma-separated values with XLOOKUP
Use this method when values such as 12 and 123 must not be treated as the same match.
FIND checks for the lookup text between comma boundaries, so wildcard characters in the lookup text are treated literally. The match is case-sensitive.
In the result cell, enter =IF($E3="","",XLOOKUP(TRUE,ISNUMBER(FIND(","&$E3&",",","&Invoice!$A$1:$A$3000&",")),Invoice!$C$1:$C$3000,""))
Enter a value in E3 and confirm that the formula returns the corresponding value from Invoice column C. Copy the formula down if other lookup values are in column E.

Use escaped wildcards for a partial-text match
Use this method when a match anywhere inside a cell is acceptable, even if it is part of a longer value.
Look up complete comma-separated values in WPS Spreadsheets
Use a helper column with FIND, then return the first matching Invoice value with INDEX and MATCH. This approach treats wildcard characters literally and avoids partial-token matches.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and go to the sheet containing the lookup value in E3.
- 2. Prepare the entries: On the Invoice sheet, check that column A contains comma-separated values without spaces immediately around the commas and that column C contains the return values.
- 3. Create a helper column: In an empty cell A3 on the lookup sheet, enter =IF($E$3="",FALSE,ISNUMBER(FIND(","&$E$3&",",","&Invoice!A1&","))) and fill it down through A3002.
- 4. Return the match: In an empty result cell, enter =IFERROR(INDEX(Invoice!$C$1:$C$3000,MATCH(TRUE,$A$3:$A$3002,0)),""). The formula returns the first matching row; FIND is case-sensitive.

Frequently Asked Questions
What does the 2 at the end of an XLOOKUP formula do?
The value 2 sets XLOOKUP's match mode to wildcard matching, allowing * and ? in the lookup pattern to match multiple or single characters.
How can I prevent 12 from matching 123?
Use the complete-value formula with FIND and comma boundaries. Make sure the source tokens have no spaces immediately around their comma delimiters.
How do I search for a literal asterisk or question mark?
In a wildcard XLOOKUP, escape ~, *, and ? in the lookup text with SUBSTITUTE, as shown in the partial-text solution. The FIND-based complete-value formula treats these characters literally.
What happens if more than one row contains the value?
XLOOKUP returns the first matching row by default. The WPS INDEX and MATCH helper-column method also returns the first match.
Is there a more reliable way to organize these values?
If possible, split the comma-separated values into separate rows or columns and use exact lookups. This avoids ambiguity caused by delimiters and overlapping text.




