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

ED	T		EH,DR,HU,YU,SE,QT
EH	F
DR	G
HU	H
SE	E
YU	U
QT	R

<tbody>
</tbody>

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