Search element in comma separated list based on substring match in Google Sheets

Question

I have list of data containing comma separated department wise orders in column-B, which is outlined in a format like:

Order_Num-X1|Dept_Name-Y1,Order_Num-X2|Dept_Name-Y2

and so on...

See the below table:

Is it possible to split and distribute the data in corresponding department column as outlined in Column-C, Column-D, Column-E? I tried as suggested in this post, But I stuck filtering a separated list stored in a single cell.

Answer 1

Try

=IFERROR(ARRAYFORMULA(query(trim(split(flatten($A$2:$A&"|"&split($B$2:$B,",")),"|")),"select Col2 where Col3='"&C$1&"' and Col1='"&$A2&"' ",0)))