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

[PabloMagnifico]

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

Sheet1 Consolidated, Year 2021, Year 2022, Year 2023, Year 2024, Year 2025, Year 2026, Year 2027, Year 2028, Year 2029, Year 2030,Totals Annual Base Rent, $ 696,461 Free Rent Concession, $ - Leasing Commission, $ (23,540) Tenant Improvements, $ (8,164) Net Cash Flow, $ 193,622 , $ 232,086 ,...

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))