Excel: TEXTJOIN with XLOOKUP

Question

I need to look up values and separate the multiple matches with TEXTJOIN.

Here is the input data on Sheet1:

ID	Name
4003	Bob3
4003	Tom3
4003	Jim3
4004	Bob4
4004	Tom4
4004	Jim4
4005	Bob5
4005	Tom5
4005	Jim5

Sheet2: with the lookup values:

Lookup
4003
4004
4005

Here is the expected output in Sheet2:

Lookup	Expected Result
4003	Bob3 | Tom3 | Jim3
4004	Bob4 | Tom4 | Jim4
4005	Bob5 | Tom5 | Jim5

Here the formula I am trying to use so far:

=TEXTJOIN(" | ",TRUE,XLOOKUP([@[ID]],Sheet1[ID],Sheet1[Name]),
 XLOOKUP([@[ID]],Sheet1[ID],Sheet1[Name]))

This returns the same item concatenated.

What am I doing wrong?

Answer 1

If you have the following Excel Table (TB_IdName) in range A1-B10:

You can try this on cell E2:

=TEXTJOIN(" | ",,FILTER(TB_IdName[Name], TB_IdName[ID]=D2))

and expand down the above formula.

Here are the lookup values and the corresponding output: