What I need is to find all the code combinations from Codes (table 1) using text string (long text string in A2) and to get all the combinations like on the Results table (table 2). For instance, you have CE1 part of string, and it can happen that has many combinations as you can see in Results...
I tried in Power Query but i cant figure out how to look for on row level when it has comma values like CE1,CE2,CE3,CE4 or else. In my case it can only match one and first value but not the whole string.
I would like to have solution in Power Query (when it is possible)!
This is formula solution:
=UNIQUE(FILTER(Table3[Ausgang];LET(X;TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2;",";"_");"_";"</s><s>")&"</s></t>";"//s"));MMULT(IFERROR(FIND(","&X&",";","&Table3[Ausgang]&",");0);SEQUENCE(COUNTA(X);;;0)));"None Found"))