PabloMagnifico
New Member
- Joined
- Jan 31, 2018
- Messages
- 4
Hi.
I'm trying to consolidate multiple years of rental analysis. Not sure if XLookup is the correct approach as it returns #Value as the value is not found when using Sum then XLookups on multiple arrays. I'm probably doing it wrong or going about it incorrectly.
Link to the file in Drive if that helps:
I'm trying to consolidate multiple years of rental analysis. Not sure if XLookup is the correct approach as it returns #Value as the value is not found when using Sum then XLookups on multiple arrays. I'm probably doing it wrong or going about it incorrectly.
Link to the file in Drive if that helps:
Rental Analysis Ex.xlsx
docs.google.com
Rental Analysis Ex.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
4 | Consolidated | Year 2021 | Year 2022 | Year 2023 | Year 2024 | Year 2025 | Year 2026 | Year 2027 | Year 2028 | Year 2029 | Year 2030 | Totals | ||||
5 | Annual Base Rent | #VALUE! | $ 232,086 | $ 239,049 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 696,461 | ||||
6 | Free Rent Concession | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||
7 | Leasing Commission | $ (23,540) | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ (23,540) | ||||
8 | Tenant Improvements | $ (8,164) | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ (8,164) | ||||
9 | ||||||||||||||||
10 | Net Cash Flow | $ 193,622 | $ 232,086 | $ 239,049 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 664,757 | ||||
11 | ||||||||||||||||
12 | ||||||||||||||||
13 | First Gen | Year 2021 | Year 2022 | Year 2023 | Year 2024 | Year 2025 | Year 2026 | Year 2027 | Year 2028 | Year 2029 | Year 2030 | Totals | ||||
14 | Annual Base Rent | $ 225,326 | $ 232,086 | $ 239,049 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 696,461 | ||||
15 | Free Rent Concession | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||
16 | Leasing Commission | $ (23,540.39) | $ (23,540) | |||||||||||||
17 | Tenant Improvements | $ (8,164.00) | $ (8,164) | |||||||||||||
18 | ||||||||||||||||
19 | Net Cash Flow | $ 193,622 | $ 232,086 | $ 239,049 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 664,757 | ||||
20 | ||||||||||||||||
21 | ||||||||||||||||
22 | Second Gen | Year 2024 | Year 2025 | Year 2026 | Year 2027 | Year 2028 | Year 2029 | Year 2030 | Year 2031 | Year 2032 | Year 2033 | Totals | ||||
23 | Annual Base Rent | $ 246,220 | $ 253,607 | $ 261,215 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 761,042 | ||||
24 | Free Rent Concession | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||
25 | Leasing Commission | $ (25,723.22) | $ (25,723) | |||||||||||||
26 | Tenant Improvements | $ (8,164.00) | $ (8,164) | |||||||||||||
27 | ||||||||||||||||
28 | Net Cash Flow | $ 212,333 | $ 253,607 | $ 261,215 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 727,155 | ||||
29 | ||||||||||||||||
30 | ||||||||||||||||
31 | Third Gen | Year 2027 | Year 2028 | Year 2029 | Year 2030 | Year 2031 | Year 2032 | Year 2033 | Year 2034 | Year 2035 | Year 2036 | Totals | ||||
32 | Annual Base Rent | $ 269,052 | $ 277,123 | $ 285,437 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 831,611 | ||||
33 | Free Rent Concession | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||
34 | Leasing Commission | $ (28,108.46) | $ (28,108) | |||||||||||||
35 | Tenant Improvements | $ (8,164.00) | $ (8,164) | |||||||||||||
36 | ||||||||||||||||
37 | Net Cash Flow | $ 232,779 | $ 277,123 | $ 285,437 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 795,339 | ||||
38 | ||||||||||||||||
39 | ||||||||||||||||
40 | Fourth Gen | Year 2027 | Year 2028 | Year 2029 | Year 2030 | Year 2031 | Year 2032 | Year 2033 | Year 2034 | Year 2035 | Year 2036 | Totals | ||||
41 | Annual Base Rent | $ 294,000 | $ 302,820 | $ 311,904 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 908,724 | ||||
42 | Free Rent Concession | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | ||||
43 | Leasing Commission | $ (30,714.88) | $ (30,715) | |||||||||||||
44 | Tenant Improvements | $ (8,164.00) | $ (8,164) | |||||||||||||
45 | ||||||||||||||||
46 | Net Cash Flow | $ 255,121 | $ 302,820 | $ 311,904 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ 869,845 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5 | E5 | =SUM(XLOOKUP($B5,$B$14:$B$46,XLOOKUP(E$4,$E$13:$N$13,$E$14:$N$44)),XLOOKUP($B5,$B$14:$B$46,XLOOKUP(E$4,$E$22:$N$22,$E$14:$N$44))) |
E6:N8,F5:N5 | F5 | =XLOOKUP($B5,$B$14:$B$46,XLOOKUP(F$4,$E$13:$N$13,$E$14:$N$44)) |