How to look up each item in a comma-separated list in Excel
Excel formula
=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"))Split the text, look up every piece and join the results: =TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?")).
- Result on the example
- Office chair, Keyboard, Headset in
G2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
TEXTSPLIT breaks "P-102, P-105, P-107" at the commas and TRIM removes the spaces, giving three SKUs. XLOOKUP looks up all three at once and returns three names, and TEXTJOIN puts them back into one cell. Unknown SKUs show "?".
Google Sheets uses SPLIT instead of TEXTSPLIT, inside ARRAYFORMULA.
=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"))Result"Office chair, Keyboard, Headset"
G1"P-102, P-105, P-107"The value in G1.
TEXTSPLIT(G1,","){1 row × 3 cols} "P-102", " P-105", " P-107"TEXTSPLIT: Splits text across columns, and rows, at the delimiters you give.- text
G1"P-102, P-105, P-107" - col_delimiter
","
- text
TRIM(TEXTSPLIT(G1,",")){1 row × 3 cols} "P-102", "P-105", "P-107"TRIM: Removes extra spaces, leaving single spaces between words.- text
TEXTSPLIT(G1,","){1 row × 3 cols} "P-102", " P-105", " P-107"
- text
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", …A range: 8 cells in one column.
B2:B98 rows × 1 col: "Desk lamp", "Office chair", "Monitor arm", "Standing desk", …A range: 8 cells in one column.
XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"){1 row × 3 cols} "Office chair", "Keyboard", "Headset"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRIM(TEXTSPLIT(G1,",")){1 row × 3 cols} "P-102", "P-105", "P-107" - lookup_array
A2:A98 rows × 1 col: "P-101", "P-102", "P-103", "P-104", … - return_array
B2:B98 rows × 1 col: "Desk lamp", "Office chair", "Monitor arm", "Standing desk", … - if_not_found
"?"
- lookup_value
TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"))"Office chair, Keyboard, Headset"TEXTJOIN: Joins text with a delimiter between items.- delimiter
", " - ignore_empty
TRUE - text1
XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"){1 row × 3 cols} "Office chair", "Keyboard", "Headset"
- delimiter
In Google Sheets
Google Sheets needs a different formula: =TEXTJOIN(", ",TRUE,ARRAYFORMULA(IFNA(VLOOKUP(TRIM(SPLIT(G1,",")),A2:B9,2,FALSE),"?"))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(A2:A9,G1)),B2:B9,"")) | Office chair, Keyboard, Headset | For Excel 2019: return the names of the SKUs found anywhere in the text. They come back in table order, and a short code like P-10 would match inside P-101. |
Common mistakes
- Without TRIM, " P-105" with its leading space doesn't match P-105.
- TEXTSPLIT needs Excel 2024 or Microsoft 365. Excel 2021 and earlier show #NAME?.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up each item in a comma-separated list
G1 holds several SKUs separated by commas. In G2, return their product names, separated by commas, in the same order.Questions
How do I add up the prices of the listed SKUs?
Sum the lookup: =SUM(XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,C2:C9)).
How do I list the results in separate cells?
Leave out TEXTJOIN; =XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?") spills across.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.