Multiple Value, Multiple Array Consolidation Help (SUM XLOOKUP?)

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:

Rental Analysis Ex.xlsx
BCDEFGHIJKLMNO
4ConsolidatedYear 2021Year 2022Year 2023Year 2024Year 2025Year 2026Year 2027Year 2028Year 2029Year 2030Totals
5Annual Base Rent#VALUE!$ 232,086$ 239,049$ -$ -$ -$ -$ -$ -$ -$ 696,461
6Free Rent Concession$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
7Leasing Commission$ (23,540)$ -$ -$ -$ -$ -$ -$ -$ -$ -$ (23,540)
8Tenant Improvements$ (8,164)$ -$ -$ -$ -$ -$ -$ -$ -$ -$ (8,164)
9
10Net Cash Flow$ 193,622$ 232,086$ 239,049$ -$ -$ -$ -$ -$ -$ -$ 664,757
11
12
13First GenYear 2021Year 2022Year 2023Year 2024Year 2025Year 2026Year 2027Year 2028Year 2029Year 2030Totals
14Annual Base Rent$ 225,326$ 232,086$ 239,049$ -$ -$ -$ -$ -$ -$ -$ 696,461
15Free Rent Concession$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
16Leasing Commission$ (23,540.39)$ (23,540)
17Tenant Improvements$ (8,164.00)$ (8,164)
18
19Net Cash Flow$ 193,622$ 232,086$ 239,049$ -$ -$ -$ -$ -$ -$ -$ 664,757
20
21
22Second GenYear 2024Year 2025Year 2026Year 2027Year 2028Year 2029Year 2030Year 2031Year 2032Year 2033Totals
23Annual Base Rent$ 246,220$ 253,607$ 261,215$ -$ -$ -$ -$ -$ -$ -$ 761,042
24Free Rent Concession$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
25Leasing Commission$ (25,723.22)$ (25,723)
26Tenant Improvements$ (8,164.00)$ (8,164)
27
28Net Cash Flow$ 212,333$ 253,607$ 261,215$ -$ -$ -$ -$ -$ -$ -$ 727,155
29
30
31Third GenYear 2027Year 2028Year 2029Year 2030Year 2031Year 2032Year 2033Year 2034Year 2035Year 2036Totals
32Annual Base Rent$ 269,052$ 277,123$ 285,437$ -$ -$ -$ -$ -$ -$ -$ 831,611
33Free Rent Concession$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
34Leasing Commission$ (28,108.46)$ (28,108)
35Tenant Improvements$ (8,164.00)$ (8,164)
36
37Net Cash Flow$ 232,779$ 277,123$ 285,437$ -$ -$ -$ -$ -$ -$ -$ 795,339
38
39
40Fourth GenYear 2027Year 2028Year 2029Year 2030Year 2031Year 2032Year 2033Year 2034Year 2035Year 2036Totals
41Annual Base Rent$ 294,000$ 302,820$ 311,904$ -$ -$ -$ -$ -$ -$ -$ 908,724
42Free Rent Concession$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
43Leasing Commission$ (30,714.88)$ (30,715)
44Tenant Improvements$ (8,164.00)$ (8,164)
45
46Net Cash Flow$ 255,121$ 302,820$ 311,904$ -$ -$ -$ -$ -$ -$ -$ 869,845
Sheet1
Cell Formulas
RangeFormula
E5E5=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:N5F5=XLOOKUP($B5,$B$14:$B$46,XLOOKUP(F$4,$E$13:$N$13,$E$14:$N$44))
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,662
Latest member
Aman1997

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top