Formula to encompass multiple references and date range

[M3dagr8]

Hello guys I am badly stuck on this situation and need help. I have 2 sheets open. Sheet one has 2 columns account number and a disconnect date and sheet 2 has 2 columns account number and payment date. I need to setup a formula so that I can bring dates from sheet 2 into sheet 1, if the date in sheet 2 is within 45 days of the matches date in sheet 1. Now the issue is that I have multiple entries from the same account number and if I use vlookup it only pulls the first date associated with the account number. I need a formula that could go down the line and use all of the dates to match the data associated with one account number.

 

[M3dagr8]

=TEXTJOIN(",",TRUE,TRANSPOSE(TEXT(FILTER(Sheet2!$B$2:$B$3047,(Sheet2!$A$2:$A$3047=Sheet1!A3)*(Sheet2!$B$2:$B$3047<=(Sheet1!B3+45))*(Sheet2!B3:$B$3047>=Sheet1!B3),""),"mm/dd/yyyy")))
This is what the formula looks like. I sent you a sample file with some made up accounts. But the actual list of accounts is quiet big

 

[KRice]

In your formula, you have a Sheet2!B3:$B$3047 but it should be Sheet2!$B$2:$B$3047...and that will produce errors when you attempt to multiple the arrays. So try:
=TEXTJOIN(",",TRUE,TRANSPOSE(TEXT(FILTER(Sheet2!$B$2:$B$3047,(Sheet2!$A$2:$A$3047=Sheet1!A3)*(Sheet2!$B$2:$B$3047<=(Sheet1!B3+45))*(Sheet2!$B$2:$B$3047>=Sheet1!B3),""),"mm/dd/yyyy")))

 

[M3dagr8]

=TEXTJOIN(",",TRUE,TRANSPOSE(TEXT(FILTER(Sheet2!$B$2:$B$3047,(Sheet2!$A$2:$A$3047=Sheet1!A2)*(Sheet2!$B$2:$B$3047<=(Sheet1!B2+45))*(Sheet2!B2:$B$3047>=Sheet1!B2),""),"mm/dd/yyyy")))

So this was the formula for cell C2 while the one I sent previously was for cell C3

 

[M3dagr8]

Ok now I fixed that issue and the N/A turned into blanks

=TEXTJOIN(",",TRUE,TRANSPOSE(TEXT(FILTER(Sheet2!$B$2:$B$3047,(Sheet2!$A$2:$A$3047=Sheet1!A2)*(Sheet2!$B$2:$B$3047<=(Sheet1!B2+45))*(Sheet2!$B$2:$B$3047>=Sheet1!B2),""),"mm/dd/yyyy")))

 

[KRice]

Yes...I understood that. But the point remained, you want to FILTER Sheet2!$B$2:$B$3047, which is a fixed range. To do that, you need to make sure that all of your filtering criteria use the same range from rows 2 to 3047. So making the change I described fixes the issue...and those ranges need to be "fixed" with the $ sign so that they do not change as the formula is copied.

 

[M3dagr8]

Yes...I understood that. But the point remained, you want to FILTER Sheet2!$B$2:$B$3047, which is a fixed range. To do that, you need to make sure that all of your filtering criteria use the same range from rows 2 to 3047. So making the change I described fixes the issue...and those ranges need to be "fixed" with the $ sign so that they do not change as the formula is copied.

Hey KRice could you check this I think its now updated:
=TEXTJOIN(",",TRUE,TRANSPOSE(TEXT(FILTER(Sheet2!$B$2:$B$3047,(Sheet2!$A$2:$A$3047=Sheet1!A2)*(Sheet2!$B$2:$B$3047<=(Sheet1!B2+45))*(Sheet2!$B$2:$B$3047>=Sheet1!B2),""),"mm/dd/yyyy")))
This has turned the cells blank

 

[KRice]

I don't see any problem with the formula. Is it working as expected?
You could add one extra feature to the formula: in the case where multiple results are returned, you could sort them from earliest to latest dates by including the SORT function:

=TEXTJOIN(",",TRUE,TRANSPOSE(TEXT(SORT(FILTER(Sheet2!$B$2:$B$3047,(Sheet2!$A$2:$A$3047=Sheet1!A2)*(Sheet2!$B$2:$B$3047<=(Sheet1!B2+45))*(Sheet2!$B$2:$B$3047>=Sheet1!B2),"")),"mm/dd/yyyy")))

 

[M3dagr8]

I don't see any problem with the formula. Is it working as expected?
You could add one extra feature to the formula: in the case where multiple results are returned, you could sort them from earliest to latest dates by including the SORT function:

=TEXTJOIN(",",TRUE,TRANSPOSE(TEXT(SORT(FILTER(Sheet2!$B$2:$B$3047,(Sheet2!$A$2:$A$3047=Sheet1!A2)*(Sheet2!$B$2:$B$3047<=(Sheet1!B2+45))*(Sheet2!$B$2:$B$3047>=Sheet1!B2),"")),"mm/dd/yyyy")))

That didn't fix the issue. I haven't really encountered empty cells, usually there is some sort of error either Ref or N/A but this is just blank

 

[KRice]

I do not understand the issue you are having. The formula will produce a blank if the filtering criteria find no rows where all three criteria are met (account is matched, payment date is on or before Disconnect +45, and payment date occurs on or after Disconnect Date). Are you seeing blanks for those rows?