I think your whole approach probably needs reworking here. Sometimes it's better to let the experts on this forum work on their own solution to the problem, rather than pressing them for your own (rather convoluted, I'm afraid to say) solution to be modified.

With that in mind, might I suggest that you ditch any further conversations re your attempted Named Range solution and instead re-post a workbook with expected results filled in in column W? Importantly, I would also recommend doing this for some entries in the Location Facility column which are different from Location 1.

Regards