CODEWITHSUNDEEP
excel
Andy
Andy

Reputation: 3

Excel array formula challenge

I'm trying to build a formula that creates a list of models, based on a series they are categorised to. Some models may belong to more than one series, so the cell may or may not have more than one series and each is separated by a comma as the delimiter. I can't use a column for each series as the number of series supported by a model is not fixed and will vary. Also the length of the name can vary and is not a fixed number of chars.

This is the formula i think should work, but doesn't, and I'm not sure why. Where am I going wrong?

{=IFERROR(INDEX(_Model,SMALL(IF(_Series=$D$2,ROW($A$1:$A$10),IF(MID(_Series,SEARCH($D$2,_Series,1),LEN($D$2))=$D$2,ROW($A$1:$A$10),"")),ROW($A1))),"")}

In the example link below, this formula is in cells F2:F11 Named ranges: _Model = A2:A11 _Series = B2:B11

Example

Upvotes: 0

Views: 49

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

If one has the Dynamic Array Formula FILTER() put this in F2:

=FILTER(_Model,ISNUMBER(SEARCH(","&$D$2&",",","&_Series&",")))

IF not Put this in F2 and copy down:

=IFERROR(INDEX(_Model,AGGREGATE(15,7,(ROW(_Series)-MIN(ROW(_Series))+1)/(ISNUMBER(SEARCH(","&$D$2&",",","&_Series&","))),ROW($ZZ1))),"")

Upvotes: 1

Related Questions