Issue with Array Formula to return list of text items separated by a comma

[vaibhavgarg]

I have a list of from and to dates of employment against a list of names in a sheet.
I have another set of from and to dates in a second sheet. I need to find out the names of people in the first sheet that were employed during that period; preferable separated by a comma.

I have array entered the following formula in a cell in sheet 2

=CONCAT(INDEX(Name,AGGREGATE(15,6,(1/(((A4<todate)*(b4>FromDate))>0))*ROW(FromDate)-2,{1,2,3,4})))

a4 and b4 are from and to dates from the second sheet respectively.

The issue is that the index function returns only the first value. I have tried transposing the array both as arguments of the aggregate function and the net output; but to no avail.

the file is here:
https://www.dropbox.com/s/izzx7isztnzejlj/test file.xlsx?dl=0

See Cell H2 in sheet "data" for this formula.

Please help in deciphering this mystery.</todate)*(b4>

 

[vaibhavgarg]

I managed to figure it out.
Actually, the INDEX function does not return an array by default and has to be coerced into doing so. I used the technique illustrated in this article.

This is what I came up with:
=TEXTJOIN(", ",TRUE,INDEX(Name,N(IF(1,AGGREGATE(15,6,(1/(((A4<todate)*(b4>FromDate))>0))*ROW(FromDate)-2,ROW(OFFSET($A$1,,,COUNT((1/(((A4<todate)*(b4>FromDate))>0))))))))))

Works like a charm. I am sure this can be simplified a lot, but this gets the job done.
Thanks.</todate)*(b4></todate)*(b4>