rcomag
New Member
- Joined
- Aug 23, 2019
- Messages
- 37
- Office Version
- 2013
- 2010
- Platform
- Windows
Book1 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
11 | ||||||||||||||||
12 | T.D. No. | Property | Value | Years From | ||||||||||||
13 | 97-2001-00206 | San Francisco | 1000 | 1973-2021 | San Francisco | 97-2001-00206 | 1999 | 5000 | ||||||||
14 | 97-2001-00206 | San Francisco | 2000 | 2022 | San Francisco | 97-2001-00206 | 2020 | 4000 | ||||||||
15 | 97-2001-00206 | San Francisco | 3000 | 2021 | San Francisco | 97-2001-00206 | 2021 | 3000 | ||||||||
16 | 97-2001-00206 | San Francisco | 4000 | 2020 | ||||||||||||
17 | 97-2001-00206 | San Francisco | 5000 | 1998 | ||||||||||||
18 | 97-2001-00206 | San Francisco | 6000 | 1994 | ||||||||||||
19 | 97-2001-00206 | San Francisco | 7000 | 1993 | ||||||||||||
20 | 97-2001-00206 | San Francisco | 8000 | 1992 | ||||||||||||
21 | 97-2001-00206 | San Francisco | 9000 | 1985 | ||||||||||||
22 | 97-2001-00206 | San Francisco | 10000 | 1974 | ||||||||||||
23 | 97-2001-00206 | San Francisco | 11000 | 1973 | ||||||||||||
24 | ||||||||||||||||
25 | ||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N13:N15 | N13 | =AGGREGATE(15,6,($E$13:$E$23)/(($B$13:$B$23=L13)*($C$13:$C$23=K13)*($H$13:$H$23<=M13)),1) |
sample AR test.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | L | M | N | O | P | Q | R | |||||||||
11 | ASSESSED | YEARS | |||||||||||||||||
12 | T.D. NO. | PROPERTY | VALUE | FROM | |||||||||||||||
21 | 97-001-00206 | San Francisco | 6,810.00 | 1973-2021 | name | value | number | year | |||||||||||
22 | 97-001-00206 | San Francisco | 6,810.00 | 2022 | =VLOOKUP(S22,B13:C126,2,FALSE) | =VLOOKUP(S22&"-"&T22,F13:J117,5,FALSE) | 97-001-00222 | 2020 | |||||||||||
23 | 97-001-00206 | San Francisco | 6,810.00 | 2021 | Rotunda | #N/A | 97-001-00222 | 1998 | |||||||||||
24 | 97-001-00206 | San Francisco | 6,810.00 | 2020 | San Francisco | #N/A | 97-001-00206 | 1999 | |||||||||||
25 | 97-001-00206 | San Francisco | 6,810.00 | 1998 | San Francisco | #N/A | 97-001-00206 | 2020 | |||||||||||
26 | 97-001-00206 | San Francisco | 3,420.00 | 1994 | San Francisco | =INDEX(J:J,MATCH(S26&T26,G:G&H:H,0)) | 97-001-00206 | 2021 | |||||||||||
27 | 97-001-00206 | San Francisco | 3,310.00 | 1993 | San Francisco | 3,420.00 | 97-001-00206 | 1994 | |||||||||||
28 | 97-001-00206 | San Francisco | 2,330.00 | 1992 | San Francisco | 3,310.00 | 97-001-00206 | 1993 | |||||||||||
29 | 97-001-00206 | San Francisco | 2,330.00 | 1985 | Añir, Celedonio | 1,530.00 | 97-001-00331 | 1993 | |||||||||||
30 | 97-001-00206 | San Francisco | 1,360.00 | 1980 | =AGGREGATE(15,6,(E24:$E$32)/(($B$21:$B$32=S30)*($C$21:$C$32=O30)*($H$21:$H$32<=T30)),1) | ||||||||||||||
31 | 97-001-00206 | San Francisco | 1,360.00 | 1974 | |||||||||||||||
32 | 97-001-00206 | San Francisco | 1,360.00 | 1973 | FOLLOWED THE FORMULA BUT IT DOES NOT GET THE CORRECT VALUE FOR THE GIVEN YEAR | ||||||||||||||
33 | 97-001-00222 | Rotunda | 4,080.00 | 1980-2021 | |||||||||||||||
34 | 97-001-00222 | Rotunda | 4,080.00 | 2022 | |||||||||||||||
35 | 97-001-00222 | Rotunda | 4,080.00 | 2021 | San Francisco | 2,330.00 | 97-001-00206 | 1985 | |||||||||||
36 | 97-001-00222 | Rotunda | 4,080.00 | 2020 | San Francisco | 3,310.00 | 97-001-00206 | 1993 | |||||||||||
37 | 97-001-00222 | Rotunda | 4,080.00 | 1998 | Rotunda | 5,670.00 | 97-001-00222 | 1993 | |||||||||||
38 | 97-001-00222 | Rotunda | 2,430.00 | 1994 | Pine Street | 32,010.00 | 97-001-00260 | 1993 | |||||||||||
39 | 97-001-00222 | Rotunda | 2,240.00 | 1993 | San Francisco | 3,310.00 | 97-001-00206 | 1993 | |||||||||||
40 | 97-001-00222 | Rotunda | 5,670.00 | 1992 | |||||||||||||||
41 | 97-001-00222 | Rotunda | 5,670.00 | 1985 | |||||||||||||||
42 | 97-001-00222 | Rotunda | 3,700.00 | 1980 | |||||||||||||||
43 | 97-001-00260 | Pine Street | 4,750.00 | 1973-2021 | |||||||||||||||
44 | 97-001-00260 | Pine Street | 4,750.00 | 2022 | |||||||||||||||
45 | 97-001-00260 | Pine Street | 4,750.00 | 2021 | |||||||||||||||
46 | 97-001-00260 | Pine Street | 4,750.00 | 2020 | |||||||||||||||
47 | 97-001-00260 | Pine Street | 4,750.00 | 1998 | |||||||||||||||
48 | 97-001-00260 | Pine Street | 2,390.00 | 1994 | |||||||||||||||
49 | 97-001-00260 | Pine Street | 2,310.00 | 1993 | |||||||||||||||
50 | 97-001-00260 | Pine Street | 5,300.00 | 1992 | |||||||||||||||
51 | 97-001-00260 | Pine Street | 5,300.00 | 1985 | |||||||||||||||
52 | 97-001-00260 | Pine Street | 3,100.00 | 1980 | |||||||||||||||
53 | 97-001-00260 | Pine Street | 32,010.00 | 1974 | |||||||||||||||
54 | 97-001-00260 | Pine Street | 32,010.00 | 1973 | |||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L23:L24 | L23 | =VLOOKUP(N23,B14:C106,2,FALSE) |
M23 | M23 | =VLOOKUP(N23&"-"&O23,E14:G97,5,FALSE) |
M24:M25 | M24 | =INDEX(G:G,MATCH(N24&O24,#REF!&E:E,0)) |
L25 | L25 | =VLOOKUP(N25,B16:C107,2,FALSE) |
L26 | L26 | =VLOOKUP(N26,B16:C32,2,FALSE) |
L27 | L27 | =VLOOKUP(N27,B21:C392,2,FALSE) |
M35:M39,M27:M29 | M27 | =AGGREGATE(14,6,($D$21:$D$392)/(($B$21:$B$392=N27)*($C$21:$C$392=L27)*($E$21:$E$392<=O27)),1) |
L28 | L28 | =VLOOKUP(N28,B21:C392,2,FALSE) |
L29 | L29 | =VLOOKUP(N29,B21:C392,2,FALSE) |
C44:C54,C34:C42,C22:C32 | C22 | =C21 |
L35:L39 | L35 | =VLOOKUP(N35,$B$21:$C$392,2,FALSE) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Book1.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | L | M | N | O | |||||||||
20 | |||||||||||||||||
21 | 97-001-00206 | San Francisco | 6810 | 1973-2021 | name | value | number | year | |||||||||
22 | 97-001-00206 | San Francisco | 6810 | 2022 | #N/A | =VLOOKUP(S22&"-"&T22,F13:J117,5,FALSE) | 97-001-00222 | 2020 | |||||||||
23 | 97-001-00206 | San Francisco | 6810 | 2021 | San Francisco | =INDEX(J:J,MATCH(S26&T26,G:G&H:H,0)) | 97-001-00206 | 2021 | |||||||||
24 | 97-001-00206 | San Francisco | 6810 | 2020 | San Francisco | 2330.00 | 97-001-00206 | 1985 | |||||||||
25 | 97-001-00206 | San Francisco | 6810 | 1998 | San Francisco | 3310.00 | 97-001-00206 | 1993 | |||||||||
26 | 97-001-00206 | San Francisco | 3420 | 1994 | Rotunda | 2240.00 | 97-001-00222 | 1993 | |||||||||
27 | 97-001-00206 | San Francisco | 3310 | 1993 | Pine Street | 2310.00 | 97-001-00260 | 1993 | |||||||||
28 | 97-001-00206 | San Francisco | 2330 | 1992 | San Francisco | 3310.00 | 97-001-00206 | 1993 | |||||||||
29 | 97-001-00206 | San Francisco | 2330 | 1985 | |||||||||||||
30 | 97-001-00206 | San Francisco | 1360 | 1980 | |||||||||||||
31 | 97-001-00206 | San Francisco | 1360 | 1974 | |||||||||||||
32 | 97-001-00206 | San Francisco | 1360 | 1973 | San Francisco | 1360.00 | 97-001-00206 | 1985 | |||||||||
33 | 97-001-00222 | Rotunda | 4080 | 1980-2021 | San Francisco | 1360.00 | 97-001-00206 | 1993 | |||||||||
34 | 97-001-00222 | Rotunda | 4080 | 2022 | Rotunda | 2240.00 | 97-001-00222 | 1993 | |||||||||
35 | 97-001-00222 | Rotunda | 4080 | 2021 | Pine Street | 2310.00 | 97-001-00260 | 1993 | |||||||||
36 | 97-001-00222 | Rotunda | 4080 | 2020 | San Francisco | 1360.00 | 97-001-00206 | 1993 | |||||||||
37 | 97-001-00222 | Rotunda | 4080 | 1998 | |||||||||||||
38 | 97-001-00222 | Rotunda | 2430 | 1994 | |||||||||||||
39 | 97-001-00222 | Rotunda | 2240 | 1993 | San Francisco | 2330.00 | 97-001-00206 | 1985 | |||||||||
40 | 97-001-00222 | Rotunda | 5670 | 1992 | San Francisco | 3310.00 | 97-001-00206 | 1993 | |||||||||
41 | 97-001-00222 | Rotunda | 5670 | 1985 | Rotunda | 5670.00 | 97-001-00222 | 1993 | |||||||||
42 | 97-001-00222 | Rotunda | 3700 | 1980 | Pine Street | 32010.00 | 97-001-00260 | 1993 | |||||||||
43 | 97-001-00260 | Pine Street | 4750 | 1973-2021 | San Francisco | 3310.00 | 97-001-00206 | 1993 | |||||||||
44 | 97-001-00260 | Pine Street | 4750 | 2022 | |||||||||||||
45 | 97-001-00260 | Pine Street | 4750 | 2021 | |||||||||||||
46 | 97-001-00260 | Pine Street | 4750 | 2020 | San Francisco | 6810.00 | 97-001-00206 | 1985 | |||||||||
47 | 97-001-00260 | Pine Street | 4750 | 1998 | San Francisco | 6810.00 | 97-001-00206 | 1993 | |||||||||
48 | 97-001-00260 | Pine Street | 2390 | 1994 | Rotunda | 4080.00 | 97-001-00222 | 1993 | |||||||||
49 | 97-001-00260 | Pine Street | 2310 | 1993 | Pine Street | 4750.00 | 97-001-00260 | 1993 | |||||||||
50 | 97-001-00260 | Pine Street | 5300 | 1992 | San Francisco | 6810.00 | 97-001-00206 | 1993 | |||||||||
51 | 97-001-00260 | Pine Street | 5300 | 1985 | |||||||||||||
52 | 97-001-00260 | Pine Street | 3100 | 1980 | |||||||||||||
53 | 97-001-00260 | Pine Street | 32010 | 1974 | |||||||||||||
54 | 97-001-00260 | Pine Street | 32010 | 1973 | |||||||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L22 | L22 | =VLOOKUP(S22,B13:C126,2,FALSE) |
L23 | L23 | =VLOOKUP(N23,B16:C32,2,FALSE) |
L46:L50,L39:L43,L32:L36,L24:L28 | L24 | =VLOOKUP(N24,$B$21:$C$392,2,FALSE) |
M24:M28 | M24 | =AGGREGATE(15,6,($D$21:$D$392)/(($B$21:$B$392=N24)*($C$21:$C$392=L24)*($E$21:$E$392>=O24)),1) |
C44:C54,C34:C42,C22:C32 | C22 | =C21 |
M32:M36 | M32 | =AGGREGATE(15,6,($D$21:$D$392)/(($B$21:$B$392=N32)*($C$21:$C$392=L32)*($E$21:$E$392<=O32)),1) |
M39:M43 | M39 | =AGGREGATE(14,6,($D$21:$D$392)/(($B$21:$B$392=N39)*($C$21:$C$392=L39)*($E$21:$E$392<=O39)),1) |
M46:M50 | M46 | =AGGREGATE(14,6,($D$21:$D$392)/(($B$21:$B$392=N46)*($C$21:$C$392=L46)*($E$21:$E$392>=O46)),1) |