9. Controlling values not included in a another table



=IFERROR(TEXTJOIN(",";TRUE;SORT(INDEX(UNIQUE(FILTER(SOURCE[BATCH];(SOURCE[Type]="")*(MONTH(SOURCE[DATE])=MONTH(TODAY()))*(SOURCE[C]=0)));;);;1));"")

    This formula is a nested IFERROR, TEXTJOIN, SORT, INDEX, UNIQUE, and FILTER function that concatenates the unique values in the column BATCH of the table SOURCE where the value in the column Type is empty and the value in the column C is 0.

    If there are no such cells, it concatenates the unique values in the column BATCH of the table SOURCE where the month in the column DATE is equal to the current month and the value in the column C is 0.

    If there are no such cells, it returns an empty string.

    Here’s how it works:

  1.  FILTER(SOURCE[BATCH];(SOURCE[Type]="")*(MONTH(SOURCE[DATE])=MONTH(TODAY()))*(SOURCE[C]=0)) returns an array of values where each value is a cell in the column BATCH of the table SOURCE where the value in the column Type is empty, in the column Date the month is the same as today and the value in the column C is 0.

  2.  UNIQUE(...) returns an array of unique values from the array returned by step 1.

  3.  INDEX(...) returns the first cell in the column BATCH of the table SOURCE.

  4.  SORT(...) sorts the array returned by step 2 in ascending order.

  5.  TEXTJOIN(", ";TRUE;...) concatenates the values in the array returned by step 4 with a comma and a space as the delimiter.

  6.  IFERROR(...) handles errors that may occur in the TEXTJOIN function. If the TEXTJOIN function returns an error, the IFERROR function returns an empty string.