VLOOKUP values separated by commas

[Tony Mac]

So the table of values are structured
CELL D1 1.1 - Value 1
CELL D2 1.1.1 - Value 2
CELL D3 2.1 - Value 3
CELL D4 2.1.1 Value 4 etc

The user enters data in a cell A1 thus eg 1.1,2.11 etc. separated by a comma. There can be multiple entries. I can't use the VLOOKUP("*" &D1& "*", A1:A10, 2, False) because it will find 1.1 when the value is actually 1.1.1. Any ideas how I get around this?

 

[shamimarita]

Hello Tony Mac,

Use an exact match lookup on the comma-separated tokens (no wildcards), e.g., with TEXTSPLIT + XLOOKUP:

=TEXTJOIN(", ",TRUE,IFERROR(XLOOKUP(TRIM(TEXTSPLIT(A1,",")),$D$1:$D$100,$E$1:$E$100),""))

This splits A1 by commas, trims spaces, then does an exact lookup for each code (so 1.1 won’t accidentally match 1.1.1).

 

[wiferetire]

Hello Tony Mac,

Use an exact match lookup on the comma-separated tokens (no wildcards), e.g., with TEXTSPLIT + XLOOKUP:

=TEXTJOIN(", ",TRUE,IFERROR(XLOOKUP(TRIM(SNAKE GAME(A1,",")),$D$1:$D$100,$E$1:$E$100),""))

This splits A1 by commas, trims spaces, then does an exact lookup for each code (so 1.1 won’t accidentally match 1.1.1).

Hi shamimarita
What if the lookup range D1100 is very large?

 

[shamimarita]

Hello,

If D1100 (or the lookup range) is very large, the formula will still work correctly, but performance can slow down because XLOOKUP is executed once for each split value.

To handle large ranges more efficiently, you can consider these tips:

• Limit the lookup range to only the rows that actually contain data (avoid whole-column references like D if possible).
• Convert the lookup range to an Excel Table and reference the table columns, this improves readability and often performance.
• If the lookup table is static, pre-clean the data (remove extra spaces, ensure exact codes) to reduce repeated TRIM or error handling.
• For very large datasets, a helper column or Power Query approach may be more efficient than doing everything inside one formula.

So yes, large ranges are fine functionally, but optimizing the range size and structure will keep the workbook fast and responsive.