I have one Sheet(Personal Cert)
there are other sheets C&L 2021, SK 2021, C&L 2020, SK 2020, ect going back to 2014
if it finds a match in any of the books im looking for the match in Colum A:A and also return O:O.
so in the search box $N$2 i type john i would like it to return all matches weather partial or exact from A:A on each sheet to $N$4 going down and each return of name also in next colum $o$4 going down bring in value in 0:0 for were there was a match return in N:N
something like this
Personal Taxes.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Enter File#: | Name Search | ||||||||||||||
2 | 663028 | <-------File Number Only | john | |||||||||||||
3 | FILE:663028 | NAME | DATE | BILL # | DISCOUNT | FACE | PENELTY | OLD *** | NEW *** | ADDRESS | Apt# | NOTE | ||||
4 | 2022 C&L | HAUER REBECCA | UNPAID | 01500135 | $60.32 | $61.55 | $67.71 | 50 | 906 E SUNBURY ST | #VALUE! | ||||||
5 | 2022 SCHOOL | HAUER REBECCA | UNPAID | 01500126 | $107.80 | $110.00 | $121.00 | 50 | 906 E SUNBURY ST | SHAMOKIN PA 17872 | ||||||
6 | ||||||||||||||||
7 | NAME | DATE | BILL # | DISCOUNT | FACE | PENELTY | OLD *** | NEW *** | ADDRESS | Apt# | NOTE | |||||
8 | 2021 C&L | HAUER REBECCA | UNPAID | 01500159 | $60.32 | $61.55 | $67.71 | 50 | 906 E SUNBURY ST | |||||||
9 | 2021 SCHOOL | HAUER REBECCA | UNPAID | 01500143 | $107.80 | $110.00 | $121.00 | 50 | 906 E SUNBURY ST | SHAMOKIN PA 17872 | ||||||
10 | ||||||||||||||||
11 | NAME | DATE | BILL # | DISCOUNT | FACE | PENELTY | OLD *** | NEW *** | ADDRESS | Apt# | NOTE | |||||
12 | 2020 C&L | HAVIER REBECCA | UNPAID | 01500165 | $60.32 | $61.55 | $67.71 | 50 | 906 E SUNBURY ST | NAME CORRECTION: HAUER, REBECCA | ||||||
13 | 2020 SCHOOL | HAUER REBECCA | UNPAID | 01500162 | $107.80 | $110.00 | $110.00 | 50 | 906 E SUNBURY ST | SHAMOKIN PA 17872 | ||||||
Personal Cert |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A3 | A3 | =CONCATENATE("FILE:",A2) |
B4 | B4 | =INDEX('[Occ County Local 2022.xls]Per Capita'!$A$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1) |
C4 | C4 | =IF(INDEX('[Occ County Local 2022.xls]Per Capita'!$C$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1)="","UNPAID",INDEX('[Occ County Local 2022.xls]Per Capita'!$C$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1)) |
D4 | D4 | =INDEX('[Occ County Local 2022.xls]Per Capita'!$B$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1) |
E4 | E4 | =INDEX('[Occ County Local 2022.xls]Per Capita'!$H$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1) |
F4 | F4 | =INDEX('[Occ County Local 2022.xls]Per Capita'!$I$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1) |
G4 | G4 | =INDEX('[Occ County Local 2022.xls]Per Capita'!$J$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1) |
H4 | H4 | =INDEX('[Occ County Local 2022.xls]Per Capita'!$N$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1) |
I4 | I4 | =INDEX('[Occ County Local 2022.xls]Per Capita'!$O$2:$Q$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),3) |
J4 | J4 | =IF(INDEX('[Occ County Local 2022.xls]Per Capita'!$O$2:$T$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),6)="","",INDEX('[Occ County Local 2022.xls]Per Capita'!$O$2:$T$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),6)) |
K4 | K4 | =IF(INDEX('[Occ County Local 2022.xls]Per Capita'!$O$2:$U$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),7)="","",INDEX('[Occ County Local 2022.xls]Per Capita'!$O$2:$U$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),7)) |
L4 | L4 | =IF(INDEX('[Occ County Local 2022.xls]Per Capita'!$G$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1)="","",INDEX('[Occ County Local 2022.xls]Per Capita'!$G$2:$O$7002,MATCH($A$3,'[Occ County Local 2022.xls]Per Capita'!$O$2:$O$7002,0),1)) |
B5 | B5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$A$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1) |
C5 | C5 | =IF(INDEX('[Occ_School 2022.xls]Per Capita'!$C$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1)="","UNPAID",INDEX('[Occ_School 2022.xls]Per Capita'!$C$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1)) |
D5 | D5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$B$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1) |
E5 | E5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$H$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1) |
F5 | F5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$I$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1) |
G5 | G5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$J$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1) |
H5 | H5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$M$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1) |
I5 | I5 | =IF(INDEX('[Occ_School 2022.xls]Per Capita'!$N$2:$P$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),3)="","",INDEX('[Occ_School 2022.xls]Per Capita'!$N$2:$P$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),3)) |
J5 | J5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$N$2:$S$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),6) |
K5 | K5 | =INDEX('[Occ_School 2022.xls]Per Capita'!$N$2:$T$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),7) |
L5 | L5 | =IF(INDEX('[Occ_School 2022.xls]Per Capita'!$G$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1)="","",INDEX('[Occ_School 2022.xls]Per Capita'!$G$2:$N$7002,MATCH($A$3,'[Occ_School 2022.xls]Per Capita'!$N$2:$N$7002,0),1)) |
N4 | N4 | = VLOOKUP(N2,'C&L 2021'!A:A,1,'[Rle_School 2022.xls]Real Estate'!$B$2422) |
B8 | B8 | =INDEX('C&L 2021'!$A$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1) |
C8 | C8 | =IF(INDEX('C&L 2021'!$C$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1)="","UNPAID",INDEX('C&L 2021'!$C$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1)) |
D8 | D8 | =INDEX('C&L 2021'!$B$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1) |
E8 | E8 | =INDEX('C&L 2021'!$I$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1) |
F8 | F8 | =INDEX('C&L 2021'!$J$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1) |
G8 | G8 | =INDEX('C&L 2021'!$K$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1) |
H8 | H8 | =INDEX('C&L 2021'!$O$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1) |
I8 | I8 | =IF(INDEX('C&L 2021'!$P$2:$R$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),3)="","",INDEX('C&L 2021'!$P$2:$R$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),3)) |
J8 | J8 | =INDEX('C&L 2021'!$P$2:$U$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),6) |
K8 | K8 | =INDEX('C&L 2021'!$P$2:$V$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),7) |
L8 | L8 | =IF(INDEX('C&L 2021'!$H$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1)="","",INDEX('C&L 2021'!$H$2:$P$5573,MATCH($A$3,'C&L 2021'!$P$2:$P$5573,0),1)) |
B9 | B9 | =INDEX('SK 2021'!$A$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1) |
C9 | C9 | =IF(INDEX('SK 2021'!$A$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),3)="","UNPAID",INDEX('SK 2021'!$A$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),3)) |
D9 | D9 | =INDEX('SK 2021'!$B$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1) |
E9 | E9 | =INDEX('SK 2021'!$H$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1) |
F9 | F9 | =INDEX('SK 2021'!$I$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1) |
G9 | G9 | =INDEX('SK 2021'!$J$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1) |
H9 | H9 | =INDEX('SK 2021'!$M$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1) |
I9 | I9 | =IF(INDEX('SK 2021'!$N$2:$P$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),3)="","",INDEX('SK 2021'!$N$2:$P$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),3)) |
J9 | J9 | =INDEX('SK 2021'!$N$2:$S$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),6) |
K9 | K9 | =INDEX('SK 2021'!$N$2:$T$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),7) |
L9 | L9 | =IF(INDEX('SK 2021'!$G$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1)="","",INDEX('SK 2021'!$G$2:$N$4596,MATCH($A$3,'SK 2021'!$N$2:$N$4596,0),1)) |
B12 | B12 | =INDEX('C&L 2020'!$A$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1) |
C12 | C12 | =IF(INDEX('C&L 2020'!$C$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1)="","UNPAID",INDEX('C&L 2020'!$C$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1)) |
D12 | D12 | =INDEX('C&L 2020'!$B$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1) |
E12 | E12 | =INDEX('C&L 2020'!$H$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1) |
F12 | F12 | =INDEX('C&L 2020'!$I$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1) |
G12 | G12 | =INDEX('C&L 2020'!$J$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1) |
H12 | H12 | =INDEX('C&L 2020'!$N$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1) |
I12 | I12 | =IF(INDEX('C&L 2020'!$O$2:$Q$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),3)="","",INDEX('C&L 2020'!$O$2:$Q$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),3)) |
J12 | J12 | =INDEX('C&L 2020'!$O$2:$U$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),7) |
K12 | K12 | =INDEX('C&L 2020'!$O$2:$V$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),8) |
L12 | L12 | =IF(INDEX('C&L 2020'!$G$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1)="","",INDEX('C&L 2020'!$G$2:$O$5569,MATCH($A$3,'C&L 2020'!$O$2:$O$5569,0),1)) |
B13 | B13 | =INDEX('SK 2020'!$A$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1) |
C13 | C13 | =IF(INDEX('SK 2020'!$C$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1)="","UNPAID",INDEX('SK 2020'!$C$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1)) |
D13 | D13 | =INDEX('SK 2020'!$B$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1) |
E13 | E13 | =INDEX('SK 2020'!$H$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1) |
F13 | F13 | =INDEX('SK 2020'!$I$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1) |
G13 | G13 | =INDEX('SK 2020'!$J$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1) |
H13 | H13 | =INDEX('SK 2020'!$M$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1) |
I13 | I13 | =IF(INDEX('SK 2020'!$N$2:$P$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),3)="","",INDEX('SK 2020'!$N$2:$P$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),3)) |
J13 | J13 | =INDEX('SK 2020'!$N$2:$T$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),7) |
K13 | K13 | =INDEX('SK 2020'!$N$2:$U$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),8) |
L13 | L13 | =IF(INDEX('SK 2020'!$G$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1)="","",INDEX('SK 2020'!$G$2:$N$5469,MATCH($A$3,'SK 2020'!$N$2:$N$5469,0),1)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'Personal Cert'!Print_Area | ='Personal Cert'!$A$3:$L$37 | B4:L5, B8:L9, B12:L13 |
'Personal Cert'!Z_6E15D8C3_F0B8_4971_8337_844C3A80FF91_.wvu.PrintArea | ='Personal Cert'!$A$3:$L$37 | B4:L5, B8:L9, B12:L13 |
there are other sheets C&L 2021, SK 2021, C&L 2020, SK 2020, ect going back to 2014
if it finds a match in any of the books im looking for the match in Colum A:A and also return O:O.
so in the search box $N$2 i type john i would like it to return all matches weather partial or exact from A:A on each sheet to $N$4 going down and each return of name also in next colum $o$4 going down bring in value in 0:0 for were there was a match return in N:N
something like this
Name Search | |
john | |
smith john | file:98652 |
john apple | File:685452 |