Extract Comma-Separated Matches Based on Multiple Criteria and Order Preservation

[sobuj53]

Hi,
I have a dataset with four columns (A:D) and a cell (E5) containing comma-separated values. I need to extract all matching values from column B that meet specific criteria while preserving the order presented in cell E5.

A	B	C	D
2	01-0012
3	01-0011		This
4	01-0013
1	01-00102		This
1	01-0045	zInf	This
7	01-0016
7	01-0014		This

Now in Cell E5 I have following value: 1, 3
I'm trying to match each value of E5 with column A, while the corresponding value of column D should contain "This" and Column C should not contain "zInf" and then return all matching value from column B.
The output should look like: 01-00102, 01-0014 , 01-0011. I have following formula,

=TEXTJOIN(", "; TRUE; FILTER(B1:B7; (ISNUMBER(MATCH(A1:A7; FILTERXML("<t><s>" & SUBSTITUTE(E5; ", "; "</s><s>") & "</s></t>"; "//s"); 0))) * (D1:D7="This") * (C1:C7<>"zInf")); "")

However this formula sorts the returned value, so I'm getting 01-0011, 01-00102, 01-0014 instead of 01-00102, 01-0014 , 01-0011. I need the retuned value in order they appear in Cell E5.

I have anther formula

=TEXTJOIN(", "; TRUE; INDEX(B1:B7; MATCH(FILTERXML("<t><s>" & SUBSTITUTE(E5; ", "; "</s><s>") & "</s></t>"; "//s"); IF((D1:D7="This")*(C1:C7<>"zInf"); A1:A7; ""); 0)))

this formula works but it only returns the first matching value, not all matching value from column B.


I'd really appreciate if someone could help me out in this matter. Thank you.

 

[sobuj53]

=TEXTJOIN(", ",TRUE,IF(A1:A7=--TEXTSPLIT(E5,",")*(D1:D7="This")*(C1:C7<>"zInf"),B1:B7,""))

Hi thank you for your response. However this also do not preserve how the value are arranged in cell E5.