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!

 

[KRice]

Go to cell E12 and delete only the first equal sign preceding the formula, then hit Enter so that Excel sees the "formula" as text....click anywhere else to be sure that you have finished editing that cell. You should then be able to read the "formula" displaying as text in cell E12. Then go back to cell E12 and type an = sign in front of the formula text and hit Enter...only Enter, nothing else, and tell me what happens.

 

[CrashOD]

1. took = out and went to different cell

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E	F	G	H
1
2							Data Sheet Names
3							SK 2021
4							SK 2020
5							SK 2019
6			parcels for testing		Choose Data Set
7			016-01-008-014		SK 2021
8			013-02-005-317
9			011-01-003-135
10
11			Parcel Look Up		Row Index of Record
12			016-01-008-014		MAX(AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))* MAX(AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))*(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})=MAX(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})))))
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E7	List	=$G$3:$G$5

2. add = back and hit enter

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E	F	G
1
2							Data Sheet Names
3							SK 2021
4							SK 2020
5							SK 2019
6			parcels for testing		Choose Data Set
7			016-01-008-014		SK 2021
8			013-02-005-317
9			011-01-003-135
10
11			Parcel Look Up		Row Index of Record
12			016-01-008-014		#VALUE!
Sheet1

Cell Formulas
Range	Formula
E12	E12	=MAX(AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))* MAX(AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))*(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})=MAX(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})))))

Cells with Data Validation
Cell	Allow	Criteria
E7	List	=$G$3:$G$5

i still get the #value

i changed th code taking out =$c$12 and put comma and not #num

 

[KRice]

I'm wondering if the VBA in the workbook is causing an issue. Try dragging the E12 cell to a different location like cell J1...and see what happens. Everytime I delete the leading = sign and re-enter it, the formula works.
I don't know that you mean by this:

i changed th code taking out =$c$12 and put comma and not #num

 

[KRice]

Go back and look at my post #69 and reproduce the formulas in H11:M11 and see if you get the same intermediate results. The formulas shown in green at the bottom of the XL2BB mini-sheet are recognized as array formulas. You may have to enter them with Ctrl-Shift-Enter.

 

[CrashOD]

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2							Data Sheet Names
3							SK 2021
4							SK 2020
5							SK 2019
6			parcels for testing		Choose Data Set
7			016-01-008-014		SK 2021
8			013-02-005-317						1
9			011-01-003-135
10
11			Parcel Look Up		Row Index of Record			1209	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
12			016-01-008-014		#VALUE!		#VALUE!
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42	SCHOOL 2021		July/Aug	Sept/Oct	Nov/Dec
43			Discount (-2%)	Face:	Penalty (+10%)
44		#VALUE!	#VALUE!	#VALUE!	#VALUE!
45	Date Paid:	#VALUE!		HomeStead	Assessed Value
46				#VALUE!	#VALUE!
47		PARTIALS	DATE		<- SHORT IN FACE
48		PAYMENT #1
49		PAYMENT #2
50		PAYMENT #3
Sheet1

Cell Formulas
Range	Formula
I8	I8	=ROW($A$2:$A$7000)-ROW($A$2)+1
H11	H11	=AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))
I11	I11	=MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})
J11	J11	=MAX(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1}) )
K11	K11	=(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1}) ) )
L11	L11	= AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))) * (MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1}) ) )
M11	M11	=MAX( AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))) * (MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1}) = MAX(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1}) ) ) )
E12,G12	E12	=MAX(AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))* MAX(AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))*(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})*MAX(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})))))
C44	C44	=INDEX(data,$E$12,31)
D44	D44	=INDEX(data,$E$12,32)
E44	E44	=INDEX(data,$E$12,33)
B44	B44	=IF(B45="","UNPAID",IF(B45="EXONERATED","PER COUNTY",IF(B45="Tax Claims", "SHORT", "PAID")))
B45	B45	=IF(D47<=0,C50,IF(D47<D44,"Tax Claims",INDEX(data,$E$12,3)))
E46	E46	=INDEX(data,$E$12,20)
D46	D46	=INDEX(data,$E$12,35)
D47	D47	=IF(D48="","",IF(OR(SUM(D48:D50)>=D44,SUM(D44,-D48:D50)<0.02),0,SUM(D44,-D48:D50)))
C48:C50	C48	=IFERROR(IF(ROWS($C$48:C48)<=COUNTA(INDEX(data,$E$12,{8,9,10,11,12,13}))/2,INDEX(data,$E$12,7+2*ROWS($C$48:C48)-1),""),"")
D48:D50	D48	=IFERROR(IF(ROWS($D$48:D48)<=COUNTA(INDEX(data,$E$12,{8,9,10,11,12,13}))/2,INDEX(data,$E$12,7+2*ROWS($D$48:D48)),""),"")
E48	E48	=IF(C48="","", "FACE")
E49:E50	E49	=IF(C49="","",IF($D$48<D49,"PENALTY","FACE"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E7	List	=$G$3:$G$5

 

[KRice]

Thank you. Can you go back into those formula components in H11:M11 and click on the formulas in the formula bar, then hit F2 to enter edit mode, then hit Ctrl-Shift-Enter...that is hold the Control key with one finger, then hold the Shift key with another, and then hit Enter and release all three...and confirm the formula is entered as an array formula (should see curly brackets automatically surround the formula in the formula bar...they are not directly typed to insert them). Out of curiosity, I opened your file in Calc, the spreadsheet application packaged with open source LibreOffice and it works fine, but the formula for row index in E12 (and the other portions of that formula that you've placed in H11:M11 should be array formulas too).