Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)

[Ashhay]

I need help with a vlookup formula.

Here's how it breaks down.

If I have, in one cell (call it D1):

EH,DR,HU,YU,SE,QT

and in a lookup table on another sheet:
A B C
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU U
7 QT R
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H,U,R

This has been discussed in previous threads however in my case column B there is over 400 rows and D1 could have up 10 values separated by commas.

 

[Domenic]

With the data as follows...

A1:D7

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"]ED[/TD]
[TD="class: xl65, width: 64"]T[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]EH,DR,HU,YU,SE,QT[/TD]
[/TR]
[TR]
[TD="class: xl65"]EH[/TD]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]DR[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]HU[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]SE[/TD]
[TD="class: xl65"]E[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]YU[/TD]
[TD="class: xl65"]U[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]QT[/TD]
[TD="class: xl65"]R[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

...first copy the following code for the custom function in a regular module (Alt+F11 > Insert > Module > Copy/Paste > Alt+Q)...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Function[/COLOR] AConcat(a [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], [COLOR=darkblue]Optional[/COLOR] Sep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "") [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=green]' Harlan Grove, Mar 2002[/COLOR]

    [COLOR=darkblue]Dim[/COLOR] Y [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]TypeOf[/COLOR] a [COLOR=darkblue]Is[/COLOR] Range [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [COLOR=darkblue]Next[/COLOR] Y
    [COLOR=darkblue]ElseIf[/COLOR] IsArray(a) [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a
            AConcat = AConcat & Y & Sep
        [COLOR=darkblue]Next[/COLOR] Y
    [COLOR=darkblue]Else[/COLOR]
        AConcat = AConcat & a & Sep
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

Then, to return the corresponding values in order as they appear in the lookup table, try the following worksheet formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(AConcat(IF(ISNUMBER(SEARCH(","&A1:A7&",",","&SUBSTITUTE(D1," ","")&",")),","&B1:B7,"")),",","",1)

To return the corresponding values in order as they appear in D1, try the following formula that also needs to be confirmed with CONTROL+SHIFT+ENTER...

=AConcat(INDEX(B1:B7,N(IF(1,MATCH(MID(SUBSTITUTE(SUBSTITUTE(D1," ",""),",",""),ROW(INDIRECT("1:"&(LEN(SUBSTITUTE(SUBSTITUTE(D1," ",""),",",""))/2)))*2-2+1,2),A1:A7,0)))),",")

Note that the second formula assumes that lookup values will always be two digits in length.

Hope this helps!