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.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[sobuj53]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have office 356 subscription.

 

[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
1	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.

Sorry I mistyped A7 value it should be 1 not 7. Now it is correct.

 

[Fluff]

Thanks for that
How about

=LET(s,--TEXTSPLIT(E5,", "),f,FILTER(A1:B7,(ISNUMBER(XMATCH(A1:A7,s)))*(D1:D7="this")*(C1:C7<>"zinf")),TEXTJOIN(", ",,SORTBY(INDEX(f,,2),XMATCH(INDEX(f,,1),s))))

 

[Scott Huish]

Perhaps:

Book1
	A	B	C	D	E	F	G
1	2	01-0012					01-0011
2	3	01-0011		This			01-00102
3	4	01-0013					01-0014
4	1	01-00102		This
5	1	01-0045	zInf	This	1,3
6	7	01-0016
7	1	01-0014		This
Sheet1

Cell Formulas
Range	Formula
G1:G3	G1	=LET(v,TOCOL(IF(A1:A7=--TEXTSPLIT(E5,",")*(D1:D7="This")*(C1:C7<>"zInf"),B1:B7,""),1),FILTER(v,v<>""))
Dynamic array formulas.

 

[sobuj53]

Thanks for that
How about

=LET(s,--TEXTSPLIT(E5,", "),f,FILTER(A1:B7,(ISNUMBER(XMATCH(A1:A7,s)))*(D1:D7="this")*(C1:C7<>"zinf")),TEXTJOIN(", ",,SORTBY(INDEX(f,,2),XMATCH(INDEX(f,,1),s))))

Thank you so much. This works perfectly. Many many thanks.

 

[sobuj53]

Perhaps:

Book1
	A	B	C	D	E	F	G
1	2	01-0012					01-0011
2	3	01-0011		This			01-00102
3	4	01-0013					01-0014
4	1	01-00102		This
5	1	01-0045	zInf	This	1,3
6	7	01-0016
7	1	01-0014		This
Sheet1

Cell Formulas
Range	Formula
G1:G3	G1	=LET(v,TOCOL(IF(A1:A7=--TEXTSPLIT(E5,",")*(D1:D7="This")*(C1:C7<>"zInf"),B1:B7,""),1),FILTER(v,v<>""))
Dynamic array formulas.

Thank you for your suggestion. This works however it returns a spilled range. However, I need them in a comma separated list.

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

I modified your formula, and this also works but not retaining the order of match.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Scott Huish]

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