Multi-Variable Conditional Join

[EYamada]

I have two tables; Table 1 contains ID codes, Table 2 contains a list of transactions with multiple ID code columns (i.e. ID code 1, ID Code 2, ID Code 3).  

I have filtered Table 1 to output a selected group of ID codes, and I want to join the two tables by the Table 1 ID code to the Table 2 ID Code 1, or the Table 1 ID code to the Table 2 ID Code 2, or...

I have come up with only two solutions, and neither is very efficient.  The first is to cascade join tools one after another.  The second to write a formula to flag each transaction containing the selected ID codes in any of the ID code columns (this is especially inefficient given the number of selected ID codes is approximately 100). 

Any thoughts on a different solution? 

EY

[BenMoss]

In your second table does each row have just one ID but it can be in one of three columns? Or might one row have an ID in each of the three columns? Just trying to clarify :)

[EYamada]

Hi BenMoss

Each row in the second table could have one or more of the IDs from the first table in any one of its three columns, and should not have the same ID in any two columns.  

Does that make sense?

EY

[BenMoss · Accepted solution]

Perhaps you could tranpose your three ID columns in the second table which will then give you one column with a long list of IDs, you could then join with your first table.

Does this work for you?

Ben

[EYamada]

Brilliant, so simple.  Thank you Ben.