CODEWITHSUNDEEP
excelvbaexcel-formulavlookup
Hamid Reza
Hamid Reza

Reputation: 3

Complicated Lookup in Excel

I have a table1 in Excel on sheet1 showing options of products separated by commas.

Model Options
G35UA A30E,F41A,F01B
G35UD A30E,F41A,F01B,PDIA
G35US A30E,F41A,K36B
G35UF A30E,XA43,L37A,J18G
G35UB A30E,F41A,F01B,FOOD,XA43,S10A

And I have a table2 on sheet2 showing the definition of options.

Option Definition
A30E Backup Camera
F41A Fire Ext
F01B Drive Tires
PDIA Factory PDI
K21D Backup Alarm
E911 Cascade SS
K36B K Pad
D59B Full Dashboard
TR96 Tail
S10A Sun Roof

I would like to see the third column on sheet1 that lookups the Definitions based on the Options.

Model Options Definition
G35UA A30E,F41A,F01B Backup Camera, Fire Ext, Drive Tires
G35UD A30E,F41A,F01B,PDIA Backup Camera, Fire Ext, Drive Tires, Factory PDI
G35US A30E,F41A,K36B Backup Camera, Fire Ext, K Pad
G35UF A30E,XA43,L37A,J18G Backup Camera
G35UB A30E,F41A,F01B,FOOD,XA43,S10A Backup Camera, Fire Ext, Drive Tires, Sun Roof

I wasn't successful with the vlookup function because the Options separated by commas and also the Find, the search and the Index and match functions didn't help because the number of options is varied. I appreciate your help in advance.

Upvotes: 0

Views: 65

Answers (1)

JvdV
JvdV

Reputation: 75860

Assuming your options are always four characters long and therefor you don't need to worry about them being a substring in another option. That way you don't need to pad them with commas for exact matching. So using Microsoft365, try:

enter image description here

Formula in C2:

=TEXTJOIN(", ",,FILTER(B$9:B$18,ISNUMBER(FIND(A$9:A$18,B2))))

In Excel 2019, youd use a CSE-entered formula, for example:

=TEXTJOIN(", ",,IF(ISNUMBER(FIND(A$9:A$18,B2)),B$9:B$18,""))

In any other case I'd advise you to start developing an UDF to mimic the TEXTJOIN() functionality.

Upvotes: 1

Related Questions