I think here I would use Data Blending approach.
It's hard to explain without the sample data though.
I would do a cross database join on Location ID and Year between your two tables, using a Left Outer join from Table 1 to Table 2.
Then you should be able to use a standard ifnull or zn function to specify a 0 instead of null.
Thanks Crystal. I will give this a try.
Thanks Abhi. If I remain stuck I will upload a workbook with a better example and data.