Excel formula to lookup a table from another table with comma separated output-CodePudding

I am trying to get a formula that looks up information from another table and populates the output with comma separated values. As shown below, I want to populate column D in Table 2 with information from Table 1. The desired output is in column E. I came up with this formula but it's only pulling one city per person.

TEXTJOIN(", ",,INDEX('Table 1'!B:B,MATCH(FILTERXML(""&SUBSTITUTE(C3,",","")&"","//m"),'Table 1'!A:A,0)))

CodePudding user response：

For you reference, you can see the screenshot below,

Solution as posted by

• Formula used in cell G3

=ARRAYTOTEXT(FILTER($B$3:$B$9,ISNUMBER(MATCH($A$3:$A$9,TEXTSPLIT(F3,,", ",1),0)),""))

Kindly change the cell range/sheet preferences as per your suit.

Edit

Thanks! It appears to be working now. However, there is one down side that I am hoping you can help with. If there is only one person listed, it's returning a #calc error. This formula only appear to be working when there are multiple staff present. Any idea on how to fix this?

• Formula used in cell G8

=TEXTJOIN(", ",,FILTER(B:B,ISNUMBER(MATCH(A:A,FILTERXML("<m><b>"&SUBSTITUTE(F8&", ",", ","</b><b>")&"</b></m>","//b"),0))))

Or,

• Formula used in cell G3

=ARRAYTOTEXT(FILTER($B:$B,ISNUMBER(MATCH($A:$A,TEXTSPLIT(F3&", ",,", ",1),0)),""))

CodePudding user response：

Try:

Formula in D3:

=TEXTJOIN(", ",,IF(ISNUMBER(XMATCH("*, "&A$3:A$9&", *",", "&C3&", ",2)),B$3:B$9,""))

Note I concatenated leading/trailing comma/space's to prevent possible false positives.

Also, in case you wish to avoid duplicates, just nest UNIQUE() in there:

=TEXTJOIN(", ",,UNIQUE(IF(ISNUMBER(XMATCH("*, "&A$3:A$9&", *",", "&C3&", ",2)),B$3:B$9,"")))