Search code examples
excelexcel-formulaxlookup

Excel: TEXTJOIN with XLOOKUP

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?

Solution

  • 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:

    sample excel file