Multiple Matches but only pulling 1st one

[CrashOD]

tried doing this codes looking for
C12 ' is the value i'm wanting to search for were they can be multi matches if there is i need B44-B46, D44-D46 have values in there
'SK Real Estate'!$U:$U ' is the location to look for the info to match to (C12)

B44 ' should show SK Real Estate'!$H:$H
'SK Real Estate'!$H:$H ' (Date) is the value i want returned if there is a value

B45 ' should show 'SK Real Estate'!$J:$J
'SK Real Estate'!$J:$J ' (Date) is the value i want returned if there is a value

B46 ' should show 'SK Real Estate'!$L:$L
'SK Real Estate'!$L:$L ' (Date) is the value i want returned if there is a value

D44 ' should show SK Real Estate'!$I:$I
'SK Real Estate'!$I:$I ' (Amount) is the value i want returned if there is a value

D45 ' should show 'SK Real Estate'!$K:$K
'SK Real Estate'!$K:$K ' (Amount) is the value i want returned if there is a value

D46 ' should show 'SK Real Estate'!$M:$M
'SK Real Estate'!$M:$M ' (Amount) is the value i want returned if there is a value

I tried the below codes with no luck. B44 & D44 get filled in but nothing more. on the picture below with the black boxes line 1170 in $U matches for the 2nd time witch should fill in B45 & d45 for 2nd payment.

=IF(ISNUMBER(MATCH('SK Real Estate'!$A:$U, $C$12)), MATCH($C$12('SK Real Estate'!$A:$U), ROW('SK Real Estate'!$A:$U)), "")

=INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),8)

=IFERROR(INDEX('SK Real Estate'!$H:$H, SMALL(IF($C$12='SK Real Estate'!$U:$U, ROW('SK Real Estate'!$U:$U)-ROW('SK Real Estate'!$U)+1), ROW(1:1))),"" )


this picture shows were im pulling data from (this exact sheet in in the same workbook im looking to put code in. why i used 'SK Real Estate'! and not reference a workbook then sheet

the picture below is were im doing the code for B44:B46 & D44:d46. yes the total due is wrong for face changing code to pull the other amount on sheet should be

321.41​

327.97​

360.77​

Thanks so much for any help!

 

[CrashOD]

i wish i did. but i don't. sorry.

 

[KRice]

That's okay...just wanted to confirm. To eliminate the blanks requires an additional check, so the formula becomes longer:

MrExcel_20220603.xlsx
	A	B	C	D
12		Description1	c2
42
43		dates		amts
44	Payment #1	1/7/2021		202
45	Payment #2
46	Payment #3
Tax Cert Form

Cell Formulas
Range	Formula
B44:B46	B44	=IFERROR(IF(ROWS(B$44:B44)<=COUNTA(INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),))/2, INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),2*ROWS(B$44:B44)-1),""),"")
D44:D46	D44	=IFERROR(IF(ROWS(D$44:D44)<=COUNTA(INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),))/2, INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),2*ROWS(D$44:D44)),""),"" )

 

[CrashOD]

=IFERROR(IF(ROWS(B$44:B44)<=COUNTA(INDEX('SK Real Estate'!$H:$M,MATCH($C$12,'SK Real Estate'!$U:$U,0),))/2, INDEX('SK Real Estate'!$H:$M,MATCH($C$12,'SK Real Estate'!$U:$U,0),2*ROWS(B$44:B44)-1),""),"")

i took out the part were it ends at a number. since more line may be added in future and i get this now for someone who made all payments only for date to see if still works then it stops for 3rd line. have not tried on the amounts yet.

Automated 2022 Tax Certification Form.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P				T	U
2	8/2/2021	F	237	#1-	PARTIALPAY	7/6/21	$109.33	08/02/2021	$109.32			321.41	109.32	360.77				11220	012-01-004-149
3	9/2/21	F	244		PARTIALPAY	7/6/21	$109.33	08/02/2021	$109.32	09/02/2021	$109.33	321.41	109.33	360.77				11220	012-01-004-149
4	7/6/21	F	244	INDISCOUNT	PARTIALPAY	7/6/21	$109.33					321.41	109.33	360.77				11220	012-01-004-149
SK Real Estate

now shows

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E
38	SCHOOL 2021		July/Aug	Sept/Oct	Nov/Dec
39			Discount (-2%)	Face:	Penalty (+10%)
40		UNPAID	$321.41	$327.97	$360.77
41	Date Paid:			HomeStead	Assessed Value
42				YES	11,220
43		PARTIALS	DATE	$0.00	<- SHORT IN FACE
44		PAYMENT #1	7/6/21	$109.33	FACE
45		PAYMENT #2	08/02/2021	$109.32	FACE
46		PAYMENT #3		$109.33
Tax Cert Form 2022-2021 (2)

Cell Formulas
Range	Formula
C40	C40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),31)
D40	D40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),32)
E40	E40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),33)
B40	B40	=IF(B41="","UNPAID",IF(B41="EXONERATED","PER COUNTY",IF(B41="Tax Claims", "SHORT", "PAID")))
B41	B41	=IF(D43<=0,C46,IF(D40>D43,"Tax Claims",INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),3)))
E42	E42	=INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),20)
D42	D42	=INDEX('SK Real Estate'!$A:$AI,MATCH(C12,'SK Real Estate'!$U:$U,0),35)
D43	D43	=IF(D44="","",IF((D44+D45+D46)>=D40,0,(D40-D44-D45-D46)))
C44:C46	C44	=IFERROR(IF(ROWS(B$44:B44)<=COUNTA(INDEX('SK Real Estate'!$H:$M,MATCH($C$12,'SK Real Estate'!$U:$U,0),))/2, INDEX('SK Real Estate'!$H:$M,MATCH($C$12,'SK Real Estate'!$U:$U,0),2*ROWS(B$44:B44)-1),""),"")
D44:D46	D44	=IFERROR(IF(ROWS(D$44:D44)<=COUNTA(INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),))/2, INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),2*ROWS(D$44:D44)),""),"" )
E44	E44	=IF(C44="","", "FACE")
E45	E45	=IF(C45="","",IF(D45=0,"UNPAID",IF(D45<=D44,"FACE",IF(D44>D45,"PENALTY"))))
E46	E46	=IF(C46="","",IF(D46=0,"UNPAID",IF(D46<=D44,"FACE",IF(D44>D46,"PENALTY"))))

Named Ranges
Name	Refers To	Cells
'SK Real Estate'!CSV49XLS_7	='SK Real Estate'!$A$3:$AG$4224	B41, D42:E42, C40:E40

 

[KRice]

I wouldn't recommend operating on a full column like that because Excel will evaluate more than a million rows for each column. Instead, pick a large row number that exceeds the largest expected size of your table. But if you do want to stay with full columns in the formulas, then you need to be consistent. Here, you've left the smaller ranges in the D44:D46 formulas:
=IFERROR(IF(ROWS(D$44:D44)<=COUNTA(INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),))/2, INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),2*ROWS(D$44:D44)),""),"" )
That means the formula returning amounts will not be synchronized to the same row as the formula returning dates.

But I don't understand something about your example. Now you are showing the match for 012-01-004-149 will occur on three different rows, but the row that contains all three partial payments is the 2nd of those rows, not the first. The formula will not find that 2nd row. It will only find the 1st row. Please review my earlier posts where I explained this point. Is this a problem, or is this example not typical of the actual data?

 

[CrashOD]

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E
38	SCHOOL 2021		July/Aug	Sept/Oct	Nov/Dec
39			Discount (-2%)	Face:	Penalty (+10%)
40		PAID	$321.41	$327.97	$360.77
41	Date Paid:	09/02/2021		HomeStead	Assessed Value
42				YES	11,220
43		PARTIALS	DATE	$0.00	<- SHORT IN FACE
44		PAYMENT #1	7/6/21	$109.33	FACE
45		PAYMENT #2	08/02/2021	$109.32	FACE
46		PAYMENT #3	09/02/2021	$109.33	FACE
Tax Cert Form 2022-2021 (2)

Cell Formulas
Range	Formula
C40	C40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),31)
D40	D40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),32)
E40	E40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),33)
B40	B40	=IF(B41="","UNPAID",IF(B41="EXONERATED","PER COUNTY",IF(B41="Tax Claims", "SHORT", "PAID")))
B41	B41	=IF(D43<=0,C46,IF(D40>D43,"Tax Claims",INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),3)))
E42	E42	=INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),20)
D42	D42	=INDEX('SK Real Estate'!$A:$AI,MATCH(C12,'SK Real Estate'!$U:$U,0),35)
D43	D43	=IF(D44="","",IF((D44+D45+D46)>=D40,0,(D40-D44-D45-D46)))
C44:C46	C44	=IFERROR(IF(ROWS(B$44:B44)<=COUNTA(INDEX('SK Real Estate'!$H1:$M7000,MATCH($C$12,'SK Real Estate'!$U1:$U7000,0),))/2, INDEX('SK Real Estate'!$H1:$M7000,MATCH($C$12,'SK Real Estate'!$U1:$U7000,0),2*ROWS(B$44:B44)-1),""),"")
D44:D46	D44	=IFERROR(IF(ROWS(D$44:D44)<=COUNTA(INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),))/2, INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),2*ROWS(D$44:D44)),""),"" )
E44	E44	=IF(C44="","", "FACE")
E45	E45	=IF(C45="","",IF(D45=0,"UNPAID",IF(D45<=D44,"FACE",IF(D44>D45,"PENALTY"))))
E46	E46	=IF(C46="","",IF(D46=0,"UNPAID",IF(D46<=D44,"FACE",IF(D44>D46,"PENALTY"))))

Named Ranges
Name	Refers To	Cells
'SK Real Estate'!CSV49XLS_7	='SK Real Estate'!$A$3:$AG$4224	B41, D42:E42, C40:E40

thanks i used 7000 since it will never reach that. thanks!

 

[KRice]

I'm happy to help...but don't forget to adjust all of your ranges to use the same indexes. Right now, you have mismatches between your C44:C46 formula and the D44:D46 formula. One uses rows from 1:7000, the other 3:100...that's a problem.

 

[CrashOD]

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E
38	SCHOOL 2021		July/Aug	Sept/Oct	Nov/Dec
39			Discount (-2%)	Face:	Penalty (+10%)
40		PAID	$191.15	$195.05	$214.56
41	Date Paid:	7/20/2021		HomeStead	Assessed Value
42				NO	5,400
43		PARTIALS	DATE		<- SHORT IN FACE
44		PAYMENT #1	7/20/2021	$65.03	FACE
45		PAYMENT #2
46		PAYMENT #3
Tax Cert Form 2022-2021 (2)

Cell Formulas
Range	Formula
C40	C40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),31)
D40	D40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),32)
E40	E40	=INDEX('SK Real Estate'!$A:$AK,MATCH(C12,'SK Real Estate'!$U:$U,0),33)
B40	B40	=IF(B41="","UNPAID",IF(B41="EXONERATED","PER COUNTY",IF(B41="Tax Claims", "SHORT", "PAID")))
B41	B41	=IF(D43<=0,C46,IF(D40>D43,"Tax Claims",INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),3)))
E42	E42	=INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),20)
D42	D42	=INDEX('SK Real Estate'!$A:$AI,MATCH(C12,'SK Real Estate'!$U:$U,0),35)
D43	D43	=IF(COUNTIF(D44:D46,""),"",IF(SUM(D44:D46)>=D40,0,D40-SUM(D44:D46)))
C44:C46	C44	=IFERROR(IF(ROWS(B$44:B44)<=COUNTA(INDEX('SK Real Estate'!$H1:$M$7000,MATCH($C$12,'SK Real Estate'!$U$1:$U$7000,0),))/2, INDEX('SK Real Estate'!$H$1:$M$7000,MATCH($C$12,'SK Real Estate'!$U$1:$U$7000,0),2*ROWS(B$44:B44)-1),""),"")
D44	D44	=IFERROR(IF(ROWS(D$44:D44)<=COUNTA(INDEX('SK Real Estate'!$H$1:$M$7000,MATCH($C$12,'SK Real Estate'!$U$1:$U$7000,0),))/2, INDEX('SK Real Estate'!$H$1:$M$7000,MATCH($C$12,'SK Real Estate'!$U$1:$U$7000,0),2*ROWS(D$44:D44)),""),"" )
E44	E44	=IF(C44="","", "FACE")
D45:D46	D45	=IFERROR(IF(ROWS(D$44:D45)<=COUNTA(INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),))/2, INDEX('SK Real Estate'!$H$3:$M$100,MATCH($C$12,'SK Real Estate'!$U$3:$U$100,0),2*ROWS(D$44:D45)),""),"" )
E45	E45	=IF(C45="","",IF(D45=0,"UNPAID",IF(D45<=D44,"FACE",IF(D44>D45,"PENALTY"))))
E46	E46	=IF(C46="","",IF(D46=0,"UNPAID",IF(D46<=D44,"FACE",IF(D44>D46,"PENALTY"))))

Named Ranges
Name	Refers To	Cells
'SK Real Estate'!CSV49XLS_7	='SK Real Estate'!$A$3:$AG$4224	B41, D42:E42, C40:E40

thanks so much i forgot to do that side. now to figure the rest out.!

 

[CrashOD]

Automated 2022 Tax Certification Form.xlsm
	H	I	J	K	L	M	N	O	P				T	U
8	09/15/2021	$21.79	10/18/2021	$21.79	10/29/2021	$21.79	64.07	$21.79	71.92				1810	013-02-005-317
9	09/15/2021	$21.79	10/18/2021	$21.79			64.07	21.79	71.92				1810	013-02-005-317
10	09/15/2021	$21.79					64.07	$21.79	71.92				1810	013-02-005-317
SK Real Estate 2

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E
38	SCHOOL 2021		July/Aug	Sept/Oct	Nov/Dec
39			Discount (-2%)	Face:	Penalty (+10%)
40		PAID	$69.38	$70.80	$77.88
41	Date Paid:	01/00/1900		HomeStead	Assessed Value
42				YES	4,100
43		PARTIALS	DATE	$0.00	<- SHORT IN FACE
44		PAYMENT #1	08/26/2021	$23.60	FACE
45		PAYMENT #2	11/29/2021	$23.60	FACE
46		PAYMENT #3		$26.60
Tax Cert Form 2022-2021 (2)

Cell Formulas
Range	Formula
C40	C40	=INDEX('SK Real Estate 2'!$A:$AK,MATCH(C12,'SK Real Estate 2'!$U:$U,0),31)
D40	D40	=INDEX('SK Real Estate 2'!$A:$AK,MATCH(C12,'SK Real Estate 2'!$U:$U,0),32)
E40	E40	=INDEX('SK Real Estate 2'!$A:$AK,MATCH(C12,'SK Real Estate 2'!$U:$U,0),33)
B40	B40	=IF(B41="","UNPAID",IF(B41="EXONERATED","PER COUNTY",IF(B41="Tax Claims", "SHORT", "PAID")))
B41	B41	=IF(D43=0, INDEX('SK Real Estate 2'!$A:$U,MATCH(C12,'SK Real Estate 2'!$U:$U,0),3), IF(D43<=0,C46,IF(D40>D43,"Tax Claims",INDEX('SK Real Estate 2'!$A:$U,MATCH(C12,'SK Real Estate 2'!$U:$U,0),3))))
E42	E42	=INDEX('SK Real Estate 2'!$A:$U,MATCH(C12,'SK Real Estate 2'!$U:$U,0),20)
D42	D42	=INDEX('SK Real Estate 2'!$A:$AI,MATCH(C12,'SK Real Estate 2'!$U:$U,0),35)
D43	D43	=IF(D40<=SUM(D44,D45,D46),0,IF(SUM(D40,-N(D44),-N(D45),-N(D46))<=0.02,0,IF(D40>=SUM(D44,D45,D46),SUM(D40,-N(D44),-N(D45),-N(D46)),"help")))
C44:C46	C44	=IFERROR(IF(ROWS(B$44:B44)<=COUNTA(INDEX('SK Real Estate 2'!$H1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),))/2, INDEX('SK Real Estate 2'!$H$1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),2*ROWS(B$44:B44)-1),""),"")
D44	D44	=IFERROR(IF(ROWS(D$44:D44)<=COUNTA(INDEX('SK Real Estate 2'!$H$1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),))/2, INDEX('SK Real Estate 2'!$H$1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),2*ROWS(D$44:D44)),""),"" )
E44	E44	=IF(C44="","", "FACE")
D45:D46	D45	=IFERROR(IF(ROWS(D$44:D45)<=COUNTA(INDEX('SK Real Estate 2'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2'!$U$2:$U$7000,0),))/2, INDEX('SK Real Estate 2'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2'!$U$2:$U$7000,0),2*ROWS(D$44:D45)),""),"" )
E45	E45	=IF(C45="","",IF(D45=0,"UNPAID",IF(D45<=D44,"FACE",IF(D44>D45,"PENALTY"))))
E46	E46	=IF(C46="","",IF(D46=0,"UNPAID",IF(D46<=D44,"FACE",IF(D44>D46,"PENALTY"))))

Named Ranges
Name	Refers To	Cells
'SK Real Estate 2'!CSV49XLS_7	='SK Real Estate 2'!$A$2:$AG$4211	B41, D42:E42, C40:E40

not bring in 3rd date on this code loos same

 

[KRice]

Could you post the portion of your data table on the SK Real Estate 2 worksheet that contains the information you are trying to obtain? You posted three lines that appear to be unrelated. Tell me the account/description you have in C12 on the Tax Cert Form sheet (that is not showing) and then show me in the SK Real Estate 2 sheet all instances where that match could occur (also not shown anywhere).

 

[CrashOD]

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E
38	SCHOOL 2021		July/Aug	Sept/Oct	Nov/Dec
39			Discount (-2%)	Face:	Penalty (+10%)
40		PAID	$64.07	$65.38	$71.92
41	Date Paid:	10/18/2021		HomeStead	Assessed Value
42				NO	1,810
43		PARTIALS	DATE	$0.00	<- SHORT IN FACE
44		PAYMENT #1	09/15/2021	$21.79	FACE
45		PAYMENT #2	10/18/2021	$21.79	FACE
46		PAYMENT #3		$21.79
Tax Cert Form 2022-2021 (2)

Cell Formulas
Range	Formula
C40	C40	=INDEX('SK Real Estate 2'!$A:$AK,MATCH(C12,'SK Real Estate 2'!$U:$U,0),31)
D40	D40	=INDEX('SK Real Estate 2'!$A:$AK,MATCH(C12,'SK Real Estate 2'!$U:$U,0),32)
E40	E40	=INDEX('SK Real Estate 2'!$A:$AK,MATCH(C12,'SK Real Estate 2'!$U:$U,0),33)
B40	B40	=IF(B41="","UNPAID",IF(B41="EXONERATED","PER COUNTY",IF(B41="Tax Claims", "SHORT", "PAID")))
B41	B41	=IF(D43=0, INDEX('SK Real Estate 2'!$A:$U,MATCH(C12,'SK Real Estate 2'!$U:$U,0),3), IF(D43<=0,C46,IF(D40>D43,"Tax Claims",INDEX('SK Real Estate 2'!$A:$U,MATCH(C12,'SK Real Estate 2'!$U:$U,0),3))))
E42	E42	=INDEX('SK Real Estate 2'!$A:$U,MATCH(C12,'SK Real Estate 2'!$U:$U,0),20)
D42	D42	=INDEX('SK Real Estate 2'!$A:$AI,MATCH(C12,'SK Real Estate 2'!$U:$U,0),35)
D43	D43	=IF(D40<=SUM(D44,D45,D46),0,IF(SUM(D40,-N(D44),-N(D45),-N(D46))<=0.02,0,IF(D40>=SUM(D44,D45,D46),SUM(D40,-N(D44),-N(D45),-N(D46)),"help")))
C44:C46	C44	=IFERROR(IF(ROWS(B$44:B44)<=COUNTA(INDEX('SK Real Estate 2'!$H1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),))/2, INDEX('SK Real Estate 2'!$H$1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),2*ROWS(B$44:B44)-1),""),"")
D44	D44	=IFERROR(IF(ROWS(D$44:D44)<=COUNTA(INDEX('SK Real Estate 2'!$H$1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),))/2, INDEX('SK Real Estate 2'!$H$1:$M$6987,MATCH($C$12,'SK Real Estate 2'!$U$1:$U$6987,0),2*ROWS(D$44:D44)),""),"" )
E44	E44	=IF(C44="","", "FACE")
D45:D46	D45	=IFERROR(IF(ROWS(D$44:D45)<=COUNTA(INDEX('SK Real Estate 2'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2'!$U$2:$U$7000,0),))/2, INDEX('SK Real Estate 2'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2'!$U$2:$U$7000,0),2*ROWS(D$44:D45)),""),"" )
E45	E45	=IF(C45="","",IF(D45=0,"UNPAID",IF(D45<=D44,"FACE",IF(D44>D45,"PENALTY"))))
E46	E46	=IF(C46="","",IF(D46=0,"UNPAID",IF(D46<=D44,"FACE",IF(D44>D46,"PENALTY"))))

Named Ranges
Name	Refers To	Cells
'SK Real Estate 2'!CSV49XLS_7	='SK Real Estate 2'!$A$2:$AG$4211	B41, D42:E42, C40:E40

Automated 2022 Tax Certification Form.xlsm
	H	I	J	K	L	M	N	O	P				T	U
8	09/15/2021	$21.79	10/18/2021	$21.79	10/29/2021	$21.79	64.07	$21.79	71.92				1810	013-02-005-317
9	09/15/2021	$21.79	10/18/2021	$21.79			64.07	21.79	71.92				1810	013-02-005-317
10	09/15/2021	$21.79					64.07	$21.79	71.92				1810	013-02-005-317
SK Real Estate 2

does it for all that did payment plan and paid all three