Formula to identify if a common account number has a corresponding date within range of another date

[eagerexceler]

Hello y'all,

I have a convoluted spreadsheet that has many rows of data that includes a common identifier in column A, a unique identifier in column D, and a unique date in column F. All the way over in column AY and AZ I need to conditionally populate the cell if the common identifier in column A is present on another row if the date in column F occurs within a set timeframe. I hope the attached data sheet helps clarify.

Basically, if cell A5 has a date within the range present in cell AY3 in any other row I need to populate cell AY5 with "Oth Present". Otherwise "Oth Not Present".

Thank you in advance!

Excel Nest If Date Within Another Date Example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
1
2
3																							1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	30	3		30	3
4	#	#-1	Provider	#2	#	Beg	End	Ind	Calc	Name	Tot1	Tot2	Act	Amt	Cat	SL	Re	CH	Ex	CY19	Mar	Pro	Ad 1	Ad 2	Ad 3	Ad 4	Ad 5	Ad 6	Ad 7	Ad 8	Ad 9	Ad 10	Ad 11	Ad 12	Ad 13	Ad 14	Ad 15	Ad 16	Ad 17	Ad 18	Ad 19	Ad 20	Ad 21	Ad 22	Ad 23	Ad 24	Ad 25	W/In 30?	W/In 3?	Ad Ex	Oth W/In 30?	Oth W/In 3?	Oth Exp
5	1	1-1	1	1	1	1/1/19	1/4/19	I	32	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	18089.4	1	b	a	1/1/2019
6	2	2-1	1	2	2	1/1/19	1/4/19	I	391	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	11151.2	1	b	a	1/1/2019
7	3	3-1	1	3	3	1/3/19	2/15/19	I	1	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	351119	1	b	a	1/3/2019
8	4	4-1	1	4	4	1/4/19	1/11/19	I	558	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	6125	1	b	a	1/4/2019
9	5	5-1	1	7	5	1/11/19	1/25/19	I	668	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	13378.4	1	b	a	11/1/2019	12/1/2019	12/20/2019
10	5	5-2	1	5	5	5/31/19	6/7/19	I	653	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	37537.6	1	b	a	11/1/2019	5/31/2019	6/28/2019
11	5	5-3	1	6	5	12/20/19	12/23/19	I	280	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	11252.5	1	b	a	11/1/2019	5/31/2019	12/20/2019
12
13
Sheet2

Cell Formulas
Range	Formula
Z5:AU11,W5:Y8,W2:AU2	W2	=IFNA(IF($H2="I",VLOOKUP($A2&"-"&W$3,$B:$F,5,FALSE),""),"")
X3:AU3	X3	=W3+1
A5:A11	A5	=E5
B5:B11	B5	=IF($H5="I",$E5&"-"&COUNTIFS($E$5:$E5,$E5,$H$5:$H5,"I"),"OP")

 

[eagerexceler]

The formula I'm trying to work with is =IF(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>VLOOKUP($A5,$A:$F,6,FALSE),"Oth Present","Oth Not Present"), but I don't think it's looking at all instances of the dates in column F.

 

[Oddball2020]

The formula I'm trying to work with is =IF(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>VLOOKUP($A5,$A:$F,6,FALSE),"Oth Present","Oth Not Present"), but I don't think it's looking at all instances of the dates in column F.

Did you mean to bind the first VLOOKUP to only look at the same row? notice $A5:$F5 is only the same row as the formula in AY5.... so for the examples you provided above, the IF statement is always IF(X+30 > X, "Oth Present").... it's always returning Oth Present because everything is just comparing a number+30 against that same number, so it's always TRUE.

 

[Habtest]

Hi, see if it works, assuming within 30 days means equal or less than 30 days before or after.

Book1
	A	B				F		H																																								AV	AW	AX	AY	AZ	BA
1
2
3																																																30	3		30	3
4	#	#-1				Beg		Ind																																								W/In 30?	W/In 3?	Ad Ex	Oth W/In 30?	Oth W/In 3?	Oth Exp
5	1	1-1				1/1/2019		I																																											Oth Not Present
6	2	2-1				1/1/2019		I																																											Oth Not Present
7	3	3-1				1/3/2019		I																																											Oth Not Present
8	4	4-1				1/4/2019		I																																											Oth Not Present
9	5	5-1				1/11/2019		I																																											Oth Present
10	5	5-2				5/31/2019		I																																											Oth Not Present
11	5	5-3				12/20/2019		I																																											Oth Not Present
12	5	5-4				2/10/2019		I																																											Oth Present
13	5	5-5				12/12/2018		I																																											Oth Present
Sheet1

Cell Formulas
Range	Formula
A5:A13	A5	=E5
B5:B13	B5	=IF($H5="I",$E5&"-"&COUNTIFS($E$5:$E5,$E5,$H$5:$H5,"I"),"OP")
F12	F12	=F9+30
F13	F13	=F9-30
AY5:AY13	AY5	=IF(ISERR(AGGREGATE(15,6,(ROW($A$5:$A$13)/($A$5:$A$13=$A5)/(ABS($F$5:$F$13-$F5)<=AY$3)),2)>0),"Oth Not Present","Oth Present")

 

[Oddball2020]

Just noticed what you said, "assuming within 30 days means equal or less than 30 days before or after." This is only looking for greater than 30d, not less than:

=IF(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>VLOOKUP($A5,$A:$F,6,FALSE),"Oth Present","Oth Not Present")

would need to include the boundary condition in the other direction as an OR (this probably won't be right for your situaiton, but just to show you how an OR inside the IF would look):

=IF(OR(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>=VLOOKUP($A5,$A:$F,6,FALSE), VLOOKUP($A5,$A5:$F5,6,FALSE)-AY$3>=VLOOKUP($A5,$A:$F,6,FALSE)), "Oth Present","Oth Not Present")

 

[eagerexceler]

Hey y'all, thank you for your help here.

Habtest - this formula mostly works, though I have to switch the "Oth Present" and "Oth Not Present". Also, I only want the formula to look at 30 days after, not before. Can't figure out how to alter this accordingly.

Thanks!

 

[Habtest]

Hey y'all, thank you for your help here.

Habtest - this formula mostly works, though I have to switch the "Oth Present" and "Oth Not Present". Also, I only want the formula to look at 30 days after, not before. Can't figure out how to alter this accordingly.

Thanks!

Hey, please see the revision, it now looks for 1 to 30 days after (both boundaries included).

Book1
	A	B				F	G	H																																								AV	AW	AX	AY	AZ
3																																																30	3		30	3
4	#	#-1				Beg		Ind																																								W/In 30?	W/In 3?	Ad Ex	Oth W/In 30?	Oth W/In 3?
5	1	1-1				1/1/2019		I																																											Oth Not Present
6	2	2-1				1/1/2019		I																																											Oth Not Present
7	3	3-1				1/3/2019		I																																											Oth Not Present
8	4	4-1				1/4/2019		I																																											Oth Not Present
9	5	5-1				1/11/2019		I																																											Oth Present
10	5	5-2				5/31/2019		I																																											Oth Not Present
11	5	5-3				12/20/2019		I																																											Oth Not Present
12	5	5-4				2/10/2019		I																																											Oth Not Present
13	5	5-5				12/12/2018		I																																											Oth Present
Sheet1

Cell Formulas
Range	Formula
F12	F12	=F9+30
F13	F13	=F9-30
AY5:AY13	AY5	=IF(ISERR(AGGREGATE(15,6,(ROW($A$5:$A$13)/($A$5:$A$13=$A5)/($F$5:$F$13-$F5<=AY$3)/($F$5:$F$13>$F5)),1)>0),"Oth Not Present","Oth Present")

 

[eagerexceler]

Hey Habtest - for some reason I'm still getting some results where the first date of an account number in column A is returning "Oth Present". Do you know why this is?

Appreciate your help!!

 

[Habtest]

For the results in #7 column AY, is there any that is not what you expect? Please note row 12 and 13 are added for illustration purpose and they are not in the data in your original post #1, and they change the result in other row (row 5).

Or if the issue is not with the mock data above, please upload relevant sample. We'd need those to understand what went wrong.