So I'm having an issue with creating a formula to make an "easy to read" chart. Here is the data I have to work with

300 some odd "sites", about 500+ doors and about 12+ badges for each door. each site has a set of data. In each set of data there are two fields. The first field is a door number and the second field is a badge number. Some doors can be opened with multiple badges.

I have my chart set up with all the doors and all the badge numbers at the top and all the sites listed on the left, each in it's own row, like this.

Door 1 | Door 1 | Door 1 | Door 1 | Door 2 | Door 2 | Door 3

Badge 1 |Badge2 |Badge3 |Badge 4|Badge1 |Badge 2| Badge4

Site Alpha
Site Bravo
Site Charlie


For each site, I want to display an X in the cell if that site has a particular door number and a badge number.

The problem I'm running into is that one door can have multiple badges to it so the formulas I've tried using arrays will find "door x" and "badge x" anywhere in the array I'm pulling from and put an X. So in the example below, since door 1 has a badge 3 in the array, it would put an X under door 2 badge 3 even though that door/badge doesn't exist at site Bravo.

Site Bravo
Door 1, Badge 1
Door 1, Badge 2
Door 1, Badge 3
Door 2, Badge 1
Door 2, Badge 4

I'm open to macros or whatever else I can use to get this job done without having to go through and manually put in thousands of Xs on the sheet.

Currently I have all the data both on one sheet and I have each site separated to it's own sheet, so whichever way works best.