Textjoin, Concat a list of emails if they are in a specific Group. For Office 2016

so, first

LOOKUP(lookup_value, lookup_vector [, result vector])

the LOOKUP function adheres to following (key) principles:

1. it assumes the values in the lookup_vector to be in ascending order at all times
2. it ignores values in lookup_vector that are not of the same data type as the lookup_value, and, critically, ignores errors
3. ultimately, it looks for the last value in the lookup_vector <= criteria and either a) returns the value or b) if the optional result vector is specified it returns the associated value from the result vector

so, taking above into account... in this specific example

a) the lookup vector can only contain 1 of 2 possible values -- namely 1 [1/TRUE] or #DIV/0! [1/FALSE]
b) the criteria / lookup_value is numeric [2] and bigger than any / all numeric values present in the lookup_vector

so, in short, we know the LOOKUP will find the last instance of 1 in the lookup_vector and, as we have specified it, return the associated value from the result vector

in essence, you're finding the last instance of E2 within the range A2:A42 and returning the associated value from C2:C42; so, if last instance of E2 were in A30 the LOOKUP would return contents of C30.

it is not particularly efficient but does the job...