IFS statement using concat

Hi Shel,

Here is the solution using TEXTJOIN() function built-in in Excel 2016 for Office365 subscribers. As I know so far it's not available for other users. However, in general better to use Get & Transform (aka Power Query) for such kind of transformations, available for all versions of Excel starting from 2013.

Okay, assuming your data located as here

enter following array formulas (i.e. using Ctrl+Shift+Enter)

in E2 (returns Member for unique combination of Member and Flag)

=IFERROR(
   INDEX(A$2:A$300,
      SMALL(
         IF(COUNTIFS(
               OFFSET($A$2:$A$300,,,ROW(A$2:A$300)-ROW(A$2)+1),$A$2:$A$300,
               OFFSET($C$2:$C$300,,,ROW($C$2:$C$300)-ROW($C$2)+1),$C$2:$C$300
            )=1,
            ROW(A$2:A$300)-ROW(A$2)+1
         ),ROWS($1:1)
      )
   ),""
)

in G2 (same as above but returns Flag)

=IFERROR(
   INDEX(C$2:C$300,
      SMALL(
         IF(COUNTIFS(
               OFFSET($A$2:$A$300,,,ROW(A$2:A$300)-ROW(A$2)+1),$A$2:$A$300,
               OFFSET($C$2:$C$300,,,ROW($C$2:$C$300)-ROW($C$2)+1),$C$2:$C$300
            )=1,
            ROW(A$2:A$300)-ROW(A$2)+1
         ),ROWS($1:1)
      )
   ),""
)

After that in F2

=TEXTJOIN(
   ", ",
   TRUE,
   IF($A$2:$A$7=E2,
      IF($C$2:$C$7=G2,$B$2:$B$7,""),
      ""
   )
)

which concatenates all drugs from column B for combination of member and flag as in E2 and G2.

After that select E2:G2 and drug selection down till first empty record appears (other words copy them down).