Return value if and offset value match

[MoOsEBoT88]

I have this formula but it’s not working

I want to check column a for holiday and if column c says buildinga and list all the current team that are on holiday

="HOLS= " &TEXTJOIN(", ",TRUE,IF(AND(A:A="HOL",C:C="buildinga"),OFFSET(A:A,0,1),""))

But I don’t get anything back?

 

[Peter_SSs]

I would strongly recommend against using whole column references like that. Surely your data is nothing like 1,000,000+ rows?

Try something like this. Where I have 1000, use a row number that is big enough to cover any expected data without being excessive.

="HOLS= " &TEXTJOIN(", ",1,FILTER(B2:B1000,(A2:A1000="HOL")*(C2:C1000="buildinga"),""))