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

Edit: Terribly sorry, I just learned that this formula must work with Office 2016

I already asked about this specific problem and got a solution by the wonderful ALiGW. The solution was this:

Please Login or Register  to view this content.

But it uses FILTER as well as TEXTJOIN which are both not available in Office 2016. I tried with INDEX and MATCH but I only get one email address but I need all the matching entries joined as shown in my sample Workbook in Cell F5 - for the example I selected the emails manually, which is obviously not practical.

I looked for FILTER alternative and found this article, but I can't seem to apply it to my case as the helper column is not well explained, at least for me.

https://exceljet.net/formula/extract...-helper-column

Your forum profile says this:

2016 Office 365

This is clearly WRONG. Which is it?

You should not have started a nes thread for this - you should simply have posted the changed requirement in the original thread.

You are missing the point. YOu must always tell us the OLDEST version that a solution will have to work with. FILTER is in 365, not Excel 2019.

Next time you start a thread, please make the constraints clear at the outset. Thank you.

In F2 copied down:

=TEXTJOIN(CHAR(10),TRUE,REPT($B$2:$B$42,$A$2:$A$42=$E2))

Please Login or Register  to view this content.

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...