TEXTJOIN - IF AND Query

[ChrisM92]

Hi,

I've got a TEXTJOIN formula to create a consolidated list of all fields with a date between 2 given dates.
It seemed to work when I first put it in and suddenly stopped and now I can't get it back ?

Any idea where I'm going wrong? Below is a simplified version of my sheet (The live one has 80 fields)

="◆"&TEXTJOIN(CHAR(10)&"◆",TRUE,IF(AND($E3:$H3>=J$1,$E3:$H3<=J$2),$E$1:$H$1,""))

 

[Fluff]

Try it like

="◆"&TEXTJOIN(CHAR(10)&"◆",TRUE,IF(($E3:$H3>=J$1)*($E3:$H3<=J$2),$E$1:$H$1,""))

 

[ChrisM92]

Thank you very much Fluff! Works perfectly with that

Any reason why that works but not the other so I know for next time?

 

[Fluff]

When using arrays you should always use

($E3:$H3>=J$1)*($E3:$H3<=J$2)

instead of And, or

($E3:$H3>=J$1)+($E3:$H3<=J$2)

instead of Or.

 

[ChrisM92]

Ahh okay

Thank you again!

 

[Fluff]

You're welcome & thanks for the feedback.