# How to look up each item in a comma-separated list in Excel

Split the text, look up every piece and join the results: `=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"))`.

**Excel:** `=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=TEXTJOIN(", ",TRUE,ARRAYFORMULA(IFNA(VLOOKUP(TRIM(SPLIT(G1,",")),A2:B9,2,FALSE),"?")))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Price |  |  | SKUs | P-102, P-105, P-107 |
| P-101 | Desk lamp | 39.99 |  |  | Products |  |
| P-102 | Office chair | 249 |  |  |  |  |
| P-103 | Monitor arm | 89.5 |  |  |  |  |
| P-104 | Standing desk | 449 |  |  |  |  |
| P-105 | Keyboard | 74.95 |  |  |  |  |
| P-106 | Webcam | 59 |  |  |  |  |
| P-107 | Headset | 129 |  |  |  |  |
| P-108 | Laptop stand | 45 |  |  |  |  |

Formula in G2: `=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"))`

Result: Office chair, Keyboard, Headset

## 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.

## Other ways

- `=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(A2:A9,G1)),B2:B9,""))`: 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?.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-comma-separated-items