Index match within a delimited cell

[kcmuppet]

Hi

I'm trying convert a variable list of strings within a table column into corresponding codes - a kind of Index Match within each cell, in which the values are separated with a delimiter (comma).

What's the best way to get the output in column F below? (I'm limited to Excel 2010 functions, and the tables are on different sheets, so I'm hoping to use structured references).

Scratch Book2.xlsx
	B	C	D	E	F
3	tbl_Lookup			tbl_Convert
4
5	Strings	Code		Input	Output
6	Some text 1	F		Some text 1, Some text 5, Some text 13, Some text 16	F, B, E, Q
7	Some text 2	G		Some text 2, Some text 6	G, F
8	Some text 3	M		Some text 3	M
9	Some text 4	N		Some text 4, Some text 13, Some text 1	N, E, F
10	Some text 5	B
11	Some text 6	F
12	Some text 7	J
13	Some text 8	S
14	Some text 9	L
15	Some text 10	C
16	Some text 11	I
17	Some text 12	O
18	Some text 13	E
19	Some text 14	D
20	Some text 15	P
21	Some text 16	Q
22	Some text 17	H
23	Some text 18	R
24	Some text 19	H
25	Some text 20	J
26	Some text 21	K
27	Some text 22	999
28	Some text 23	G
29	Some text 24	A
30	Some text 25	888
Sheet3

 

[kcmuppet]

I suppose using Power Query with the Convert table I could
1. Add an Index column
2. Split the Input column into rows with custom delimeter ", ",
3. merge a Lookup table using a Left outer join type,
4. Expand selecting only the Code column
5. re Group by the Index column

...but I'd prefer to keep it all in Excel if it's possible...

Any tips greatly appreciated

 

[dcuser]

Hi kcmuppet,

I need to perform an Index Match analogous to the problem you outline above. Did you happen to figure out a solution?

Thanks.

dcuser

 

[kevin9999]

@kcmuppet are you still using 2010? If so, try the following UDF in a standard module:

Function kcmuppet(convert As Range, look As Range) As String
    Dim temp, i As Long, s As String
    temp = Split(convert, ",")
    On Error Resume Next
    For i = LBound(temp) To UBound(temp)
        s = s & ", " & Trim(Application.WorksheetFunction.VLookup(Trim(temp(i)), look, 2, 0))
    Next i
    On Error GoTo 0
    kcmuppet = Mid(s, 3)
End Function

Used like this (note the absolute range reference):

Book1
	B	C	D	E	F
5	Strings	Code		Input	Output
6	Some text 1	F		Some text 1, Some text 5, Some text 13, Some text 16	F, B, E, Q
7	Some text 2	G		Some text 2, Some text 6	G, F
8	Some text 3	M		Some text 3	M
9	Some text 4	N		Some text 4, Some text 13, Some text 1	N, E, F
10	Some text 5	B
11	Some text 6	F
12	Some text 7	J
13	Some text 8	S
14	Some text 9	L
15	Some text 10	C
16	Some text 11	I
17	Some text 12	O
18	Some text 13	E
19	Some text 14	D
20	Some text 15	P
21	Some text 16	Q
22	Some text 17	H
23	Some text 18	R
24	Some text 19	H
25	Some text 20	J
26	Some text 21	K
27	Some text 22	999
28	Some text 23	G
29	Some text 24	A
30	Some text 25	888
Sheet1

Cell Formulas
Range	Formula
F6:F9	F6	=kcmuppet(E6,$B$6:$C$30)

 

[SunnyAlv]

Try this

=LET(input,TEXTSPLIT(E3,", "),lkp,XLOOKUP(input,$B$3:$B$27,C$3:C$27,""),TEXTJOIN(", ",,lkp))

 

[Peter_SSs]

@kevin9999
Not sure if you noticed but kcmuppet's question is over 3 years old.

@dcuser
Welcome to the MrExcel board!

If you wish to use the structured references of a formal table like the original question then try this version. It would automatically expand/contract if the tables change size.
I have included two options. The first if tbl_Lookup does only have two columns. The second if tbl_Lookup has more than two columns.

kcmuppet.xlsm
	B	C	D	E	F	G
3	tbl_Lookup			tbl_Convert
4
5	Strings	Code		Input	Output 1	Output 2
6	Some text 1	F		Some text 1, Some text 5, Some text 13, Some text 16	F , B , E, Q	F , B , E, Q
7	Some text 2	G		Some text 2, Some text 6	G , F	G , F
8	Some text 3	M		Some text 3	M	M
9	Some text 4	N		Some text 4, Some text 13, Some text 1	N , E, F	N , E, F
10	Some text 5	B
11	Some text 6	F
12	Some text 7	J
13	Some text 8	S
14	Some text 9	L
15	Some text 10	C
16	Some text 11	I
17	Some text 12	O
18	Some text 13	E
19	Some text 14	D
20	Some text 15	P
21	Some text 16	Q
22	Some text 17	H
23	Some text 18	R
24	Some text 19	H
25	Some text 20	J
26	Some text 21	K
27	Some text 22	999
28	Some text 23	G
29	Some text 24	A
30	Some text 25	888
Sheet1

Cell Formulas
Range	Formula
F6:F9	F6	=TEXTJOIN(", ",1,VLOOKUP(TEXTSPLIT([@Input],", "),tbl_Lookup,2,0))
G6:G9	G6	=TEXTJOIN(", ",1,XLOOKUP(TEXTSPLIT([@Input],", "),tbl_Lookup[Strings],tbl_Lookup[Code]))

 

[dcuser]

Thank you all for posting your solutions! I will give them a shot tomorrow at work. Cheers.