Function Problems

Use XLOOKUP to Find Comma-Separated Values in Excel

Rana GarciaRana Garcia Nov 18, 2026 869 views

Question details

Find a lookup value within comma-separated entries and return the corresponding result from another column.

Find Comma-Separated Values with XLOOKUP in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

In the result cell, enter =IF($E3="","",XLOOKUP(TRUE,ISNUMBER(FIND(","&$E3&",",","&Invoice!$A$1:$A$3000&",")),Invoice!$C$1:$C$3000,""))

2
Check the result

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.

Match complete comma-separated values with XLOOKUP
Match requirements: Entries must not have spaces around their comma delimiters, and E3 must match the token's capitalization. XLOOKUP returns the first matching row.
Find values in WPS Spreadsheets

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and go to the sheet containing the lookup value in E3.
  2. 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. 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. 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.
Matches complete comma-separated tokens rather than overlapping partial valuesOpens and edits Microsoft Excel XLSX filesUses a visible helper column to make matches easy to checkProvides a familiar spreadsheet interface
microsoft office alternative - wps office

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.