I'm pretty new with ArrayFormula, have been trying but sometime the formula works, sometimes does not. What I'm trying to do is the combination of ArrayFormula, Countif for searching partial text.

As shown in the worksheet below, there are 10 subjects (column A), each subject has at least one of 4 samples (A,B,C,D) summarized as a string (column B). What I'm trying to do is to find which subject has sample A or B or C or D.

I have tried single formula for each sample, eg cell D3

=IF(COUNTIF($B3,"*"&$D$2&"*")>0,$A3,"")

it returns the correct results. However, when I try arrayformula in cell I3,

=arrayformula(IF(COUNTIF($B3:B,"*"&$D$2&"*")>0,$A3:A,""))

The answers are weird. For example: Subjects (Gamma, Zeta, Eta, Theta) who don't have the sample "A" are shown to have sample "A". And this applies to sample B,C,D too

Not sure what went wrong in here.

CodePudding user response：

try:

=INDEX(QUERY(IFERROR(TRIM(SPLIT(FLATTEN(IF(IFERROR(SPLIT(B3:B, ","))="",,
 SPLIT(B3:B, ",")&"×"&A3:A)), "×"))), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"))

or use in row 2 if you want to sort it as in your example:

=INDEX(IFNA(VLOOKUP(A2:A, QUERY(IFERROR(TRIM(SPLIT(FLATTEN(
 IF(IFERROR(SPLIT(B3:B, ","))="",,SPLIT(B3:B, ",")&"×"&A3:A)), "×"))), 
 "select Col2,max(Col2) where Col2 is not null group by Col2 
  pivot Col1 label Col2'Subjects'"), {2,3,4,5}, 0)))