Two-way XLOOKUP() w/ Multiple Lookup Values (Range)

[TheMacroNoob]

Hello excel experts,

I am using a two-way XLOOKUP() to grab a range of lookup values from a dynamic column lookup.

The formula I am currently using is:
=SUM(XLOOKUP($C$11:$C$43,INDIRECT("'"&G$10&"'!"&"$A:$A"),XLOOKUP($D11,INDIRECT("'"&G$10&"'!"&"$6:$6"),INDIRECT("'"&G$10&"'!"&"$1:$1048576"))))

The INDIRECT() formulas are for a dynamic sheet reference, otherwise it would simply look like this:
=SUM(XLOOKUP($C$11:$C$43,'2018'!$A:$A,XLOOKUP($D11,'2018'!$6:$6,'2018'!$1:$1048576)))

The problem is, once the formula comes up on sheet that is missing one or more of the lookup values in the range $C$11:$C$43, an #N/A error is returned.

How can I make the formula skip or replace with 0 any missing values in the lookup range? Does the entire formula need to be scrapped and re-worked?

Expense Summary.xlsx
	C	D	E	F	G	H	I	J
10					2018	2019	2020	2021
11	5139-9998	107			2753804	2758248	3277774	#N/A
12	5159-9999	109			4121190	4220944	4550497	#N/A
13	5189-9998	110			2693655	2574210	2589297	#N/A
14	5162-0000	121			3297540	3496641	4173730	#N/A
15	5163-0000	165			3151280	3194531	3503053	#N/A
16	5180-0000	328			3989631	3825684	3741355	#N/A
17	5181-0000	331			1919973	1912210	1919059	#N/A
Sheet1

Cell Formulas
Range	Formula
G11:J17	G11	=SUM(XLOOKUP($C$11:$C$43,INDIRECT("'"&G$10&"'!"&"$A:$A"),XLOOKUP($D11,INDIRECT("'"&G$10&"'!"&"$6:$6"),INDIRECT("'"&G$10&"'!"&"$1:$1048576"))))

 

[TheMacroNoob]

Okay, I overlooked the logic of the XLOOKUP's "if not found" argument. I needed to put 0 in a couple positions in the "if not found" XLOOKUP functions.
=SUM(XLOOKUP(IF($C$6="All",$C$11:$C$43,INDEX($C$11:$C$43,XMATCH($C$6,$B$11:$B$43))),INDIRECT("'"&G$10&"'!"&"$A:$A"),XLOOKUP($D11,INDIRECT("'"&G$10&"'!"&"$6:$6"),INDIRECT("'"&G$10&"'!"&"$1:$1048576"),0),0))