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.

 

[KRice]

I don't quite understand what you want. Given this for Sheet2:

Book1
	A	B
1	Account Nbr	Original Payment Date
2	7016168	4/19/2022
3	9813036	4/22/2022
4	9813036	7/4/2022
5	19086330	7/19/2022
6	19086330	7/3/2022
7	20086330	6/20/2022
8	31112372	10/17/2022
9	31112372	3/14/2022
10	31112372	4/26/2022
Sheet2

One approach to return what you've described is shown here, but note that you can get multiple results (so I've concatenated the dates, separated by commas). Let me know if your filtering criteria need to be revised:

Book1
	A	B	C
1	Account Nbr	Disconnect Date	Original Payment Date
2	9813036	8/23/2022	04/22/2022, 07/04/2022
3	9813036	6/23/2022	04/22/2022, 07/04/2022
4	9813036	4/11/2022	04/22/2022
5	9813036	2/21/2022
6	9813036	1/3/2022
7	9813036	6/15/2022	04/22/2022, 07/04/2022
8	20086330	8/10/2022	06/20/2022
9	31112372	8/11/2022	03/14/2022, 04/26/2022
10	31112372	8/24/2022	03/14/2022, 04/26/2022
11	31112372	6/8/2022	03/14/2022, 04/26/2022
12	31112372	4/14/2022	03/14/2022, 04/26/2022
13	31112372	2/28/2022	03/14/2022
14	31112372	1/10/2022
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=TEXTJOIN(", ",TRUE,TRANSPOSE(TEXT(FILTER(Sheet2!$B$2:$B$10,(Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10<=(B2+45)),""),"mm/dd/yyyy")))

 

[M3dagr8]

Hey KRice sorry if I wasn't clear enough with my issue. I will try to explain it again I have pulled data from 2 sources for sheet 1 it was disconnection date and for sheet 2 it was original payment date. Both sources include show data as line item entries, so it would have an account number and when either the original payment was or disconnection was. I need to figure out a method where I am able to identify if a payment was made within 45 days of a disconnection date. So the formula in sheet 1 in cell C2 would be able to only bring a date from sheet 2 if (1) the account numbers match and (2) the original payment date is within 45 days of the disconnection date. The issue is there are multiple line item entries made on both sheets per account. I really liked your concatenated approach! But to be truly honest I had a hard time understanding your formula (since all of this is not in my ball park yet!) Your help would be greatly appreciated.

 

[KRice]

Thanks for the clarification. Could you go a little further, please? I'm trying to determine the specific date criteria. Are you saying that for any given Disconnect Date (let's call this date D), if we go back 45 days (let's call this date D-45), you want to consider any Payment Dates made between D-45 and D? Or are you saying to go forward 45 days from D (let's call this D+45), and we want any Payment Dates between D and D+45? Or do you want to go in both directions and consider any Payment Date between D-45 and D+45?

 

[M3dagr8]

Thanks for the clarification. Could you go a little further, please? I'm trying to determine the specific date criteria. Are you saying that for any given Disconnect Date (let's call this date D), if we go back 45 days (let's call this date D-45), you want to consider any Payment Dates made between D-45 and D? Or are you saying to go forward 45 days from D (let's call this D+45), and we want any Payment Dates between D and D+45? Or do you want to go in both directions and consider any Payment Date between D-45 and D+45?

Yes so I need it to go forward. So 45 days after the disconnection date that is where the original payment should fall

 

[KRice]

Thanks for the clarification. I made one adjustment, adding one condition to the FILTER criteria constraining the Payment Date to occur on or after the Disconnect Date. So the three FILTER criteria are to 1) match the Account Number, 2) Payment occurs on or after Disconnect Date, and 3) Payment occurs on or before the Disconnect Date + 45 days. In this example, there are not multiple returns, so TEXTJOIN may not be necessary as a way of stringing together multiple returns. In reality, will there ever be two or more answers that might justify keeping TEXTJOIN...or would the internal FILTER part of the formula be sufficient to return either one date or a blank? If no more than one date will ever be returned, the version of the formula in the blue cells would be simpler.

MrExcel_20220909.xlsx
	A	B	C	D
1	Account Nbr	Disconnect Date	Original Payment Date
2	9813036	8/23/2022
3	9813036	6/23/2022	07/04/2022	7/4/2022
4	9813036	4/11/2022	04/22/2022	4/22/2022
5	9813036	2/21/2022
6	9813036	1/3/2022
7	9813036	6/15/2022	07/04/2022	7/4/2022
8	20086330	8/10/2022
9	31112372	8/11/2022
10	31112372	8/24/2022
11	31112372	6/8/2022
12	31112372	4/14/2022	04/26/2022	4/26/2022
13	31112372	2/28/2022	03/14/2022	3/14/2022
14	31112372	1/10/2022
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=TEXTJOIN(", ",TRUE,TRANSPOSE(TEXT(FILTER(Sheet2!$B$2:$B$10,(Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10<=(B2+45))*(Sheet2!$B$2:$B$10>=B2),""),"mm/dd/yyyy")))
D2:D14	D2	=FILTER(Sheet2!$B$2:$B$10,(Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10<=(B2+45))*(Sheet2!$B$2:$B$10>=B2),"")

 

[M3dagr8]

Thank you soo much Krice! The version with 2 potential answers seems to be what I was looking for. You have really saved me a massive headache with this!!!

 

[KRice]

You're welcome...I'm happy to help. I wasn't sure about the number of dates that might be returned. The FILTER formula will return them, but you will get a spill error because there isn't room for the results to spill downward. You could wrap the FILTER function with a TRANSPOSE to get multiple results to spill to the right across multiple columns (if that is desired)...that would look like =TRANSPOSE(FILTER(...))
But if you might have multiple dates returned and you want all of them consolidated in a single cell, the TEXTJOIN offering would be preferred.

 

[M3dagr8]

Hey KRice I have an issue coming up. When I insert the text join formula I keep getting the #N/A error and when I use the Filter formula wrapped in transpose then it gives me a blank cell. I've looked over your formula many times and the issue still persists. Any tips?

 

[KRice]

Could you go to a cell where this error appears, copy the formula, and then paste it into a post?