I need to look up values and separate the multiple matches with TEXTJOIN.
Sheet1:
ID Concatenated Column
4003
4004
4005
Sheet2:
ID Name
4003 Bob
4003 Tom
4003 Jim
The "Concatenated Column" should return:
Bob | Tom | Jim
Formula so far:
=TEXTJOIN(" | ",TRUE,XLOOKUP([@[ID]],Sheets[ID],Sheets[Name]),XLOOKUP([@[ID]],Sheets[ID],Sheets[Name]))
This returns the same item concatenated.
What am I doing wrong?
CodePudding user response：
If you have the following Excel Table (TB_IdName) in range A1-B10:
| ID | Name |
|---|---|
| 4003 | Bob3 |
| 4003 | Tom3 |
| 4003 | Jim3 |
| 4004 | Bob4 |
| 4004 | Tom4 |
| 4004 | Jim4 |
| 4005 | Bob5 |
| 4005 | Tom5 |
| 4005 | Jim5 |
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:
| Lookup | Result |
|---|---|
| 4003 | Bob3 | Tom3 | Jim3 |
| 4004 | Bob4 | Tom4 | Jim4 |
| 4005 | Bob5 | Tom5 | Jim5 |
Where Lookup column is in D1:D4 range and the Result column in E1:E4 range.