Getting formula to calculate conditionally based on number of account occurrences

[eagerexceler]

Hi y'all,

I have a formula in column AV of my spreadsheet that mostly does what I want by conditionally comparing dates in other columns and populating the value from column S accordingly. Column AV is populating if a date comparison is true.

The condition that I'm trying to add to this formula is that I only want it to populate the value in column S for the first occurrence of a true date comparison from columns W through AC. So from the table example, I want AV5 to populate with the value from S5 since the date comparison is true, but I don't want AV6 to populate. The same is true for AV7 and AV8, and AV11 and AV12.

BCBS Readmissions and Pre Admission Testing By MRN - Combined - BSLMC.xlsx
	A	B		D	E	F	G	H											S				W	X	Y	Z	AA	AB	AC																			AV
3																							1	2	3	4	5	6	7																			30
4	Common #	Common # - #		Acct	Common #	Begin	End	I/P											Exp				I 1	I 2	I 3	I 4	I 5	I 6	I 7																			Acct W/In 30 Days?
5	609	609-1		845	609	1/2/19	1/6/19	I											6922.21				1/2/2019	1/23/2019	4/29/2019	5/23/2019	9/30/2019	7/26/2020	8/26/2020																			6922.21
6	609	609-2		846	609	1/23/19	2/1/19	I											6922.21				1/2/2019	1/23/2019	4/29/2019	5/23/2019	9/30/2019	7/26/2020	8/26/2020																			6922.21
7	609	609-3		847	609	4/29/19	5/5/19	I											9691.38				1/2/2019	1/23/2019	4/29/2019	5/23/2019	9/30/2019	7/26/2020	8/26/2020																			9691.38
8	609	609-4		848	609	5/23/19	5/26/19	I											9691.38				1/2/2019	1/23/2019	4/29/2019	5/23/2019	9/30/2019	7/26/2020	8/26/2020																			9691.38
9	609	609-5		849	609	9/30/19	10/7/19	I											9691.38				1/2/2019	1/23/2019	4/29/2019	5/23/2019	9/30/2019	7/26/2020	8/26/2020																			9691.38
10	609	P		850	609	10/9/20	10/9/20	P											2560				0	0	0	0	0	0	0																			0.00
11	609	609-6		851	609	7/26/20	7/31/20	I											9981.82				1/2/2019	1/23/2019	4/29/2019	5/23/2019	9/30/2019	7/26/2020	8/26/2020																			9981.82
12	609	609-7		852	609	8/26/20	9/3/20	I											11728.64				1/2/2019	1/23/2019	4/29/2019	5/23/2019	9/30/2019	7/26/2020	8/26/2020																			11728.64
Sheet1

Cell Formulas
Range	Formula
X3:AC3	X3	=W3+1
A5:A12	A5	=E5
B5:B12	B5	=IF($H5="I",$E5&"-"&COUNTIFS($E$5:$E5,$E5,$H$5:$H5,"I"),"P")
W5:W12	W5	=IFNA(IF($H5="I",VLOOKUP($A5&"-"&W$3,$B:$F,5,FALSE),"0"),0)
X5:AC12	X5	=IFNA(IF($H5="I",VLOOKUP($A5&"-"&X$3,$B:$F,5,FALSE),"0"),"0")
AV5:AV12	AV5	=IF(AND($H5="I",((OR(($W5+AV$3>=$X5),$X5+AV$3>=$Y5,$Y5+AV$3>=$Z5,$Z5+AV$3>=$AA5,$AA5+AV$3>=$AB5,$AB5+AV$3>=$AC5,)))),$S5, 0)

Hope this makes sense. Any ideas are greatly appreciated.