textjoin formula with multiple if statement not working properly specially start date to end date results not appear in formula result

[chnder]

my arry tet join formula is {=TEXTJOIN(",", TRUE,IF( F5:F12 >= K5, IF(G5:G12 <=L5, IF(J5="REVENUE", IF(A5 >=H5:I12, IF(A5<=I5:I12, D5:D12,""),""),""),""),""))}

A

B

C

D

E

F

G

H

I

J

K

L

M

A/C no.

Names

Designation

tenure From

tenure upto

start a/c no

end a/c no.

section

defaluting start

defaluting end

name of all defaulters work during tenure

15010101​

AAAA

TH

28-09-2019

30-09-2019

15000000​

16000000​

revenue

29-09-2019​

30-04-2023​

BBBBB,CCCCC,DDDDD,FFFFF,GGGGG,HHHHH

16050225​

BBBBB

TH

01-10-2019

30-04-2020

15000000​

16000000​

revenue

02-04-2021​

30-04-2023​

CCCCC

TH

01-04-2020

30-09-2020

15000000​

16000000​

DDDDD

TH

01-10-2020

30-04-2021

15000000​

16000000​

EEEEE

TH

01-04-2021

30-09-2021

16000000​

17000000​

FFFFF

TH

01-10-2021

30-04-2022

15000000​

16000000​

GGGGG

TH

01-04-2022

30-09-2022

15000000​

16000000​

HHHHH

TH

01-10-2022

30-04-2023

15000000​

16000000​

 

[Alex Blakenburg]

Does this get you what you are after ?

=TEXTJOIN(",",TRUE,FILTER($D$2:$D$9,($F$2:$F$9>=K2)*($G$2:$G$9<=L2)*($J$2:$J$9="revenue")*($H$2:$H$9<=A2)*($I$2:$I$9>=A2),""))

 

[chnder]

Does this get you what you are after ?

=TEXTJOIN(",",TRUE,FILTER($D$2:$D$9,($F$2:$F$9>=K2)*($G$2:$G$9<=L2)*($J$2:$J$9="revenue")*($H$2:$H$9<=A2)*($I$2:$I$9>=A2),""))

filter formula not in my excel ,so formula not run , please help

 

[Peter_SSs]

Welcome to the MrExcel board!

filter formula not in my excel ,so formula not run , please help

1. Filter is a standard function in MS365. Have you checked for any updates for your Excel? Does that mean that your account details showing that your version is 365 is incorrect? If so, it would be a good idea to amend your details.
   
   
2. If the formula does not 'run' then presumably it is not the solution to your problem, so I have removed the 'Mark as solution' from Alex's post for now.