Exclude data conflicts and then match remaining Data

Hello again all,

I have another matching puzzle to solve, previously Jindon was able to solve my 2 way preference matching, Thanks again @Jindon!! but this time I have a some other variables with 1 way preferences.

Ok...

My goal is to Match a list of 1-10000 USERID's with an appropriate SITEID.

I have a full table of SITEID's.

The USERID data will come in as a row in Excel, which shows:
1. USERID (Which is Numeric)
2. States of Australia to Exclude (could also be none)
3. 0 - 50 SITEID's to Exclude (alpha/numeric and numeric)
4. 0 - 5 SITEID Preferences (alpha/numeric and numeric)


My Matching algorithm includes the following rules:
1. Exclude USERID States from corresponding list of SITEID States (For example if a USERID inputs NSW and VIC, they will be excluded from all SITEID's in these states, could also have none)
2. Exclude USERID Conflict SITEID's A (e.g. If a USERID inputs 0-50 SITEID's, they will be excluded from their choice of SITEID's)
3. Match a USERID to SITEID Preference Match - they may input 0-5 preferences, (e.g. If the USERID's preference 1 matches a SITEID from the remaining list of SITEID's let over after Exclusions then Show the match in a new Cell, (Preferences have order of priority)
4. If there are no SITEID's to match the preferences show "No Match"

So a full Example might be:
USERID 111 identifies NSW and VIC to be excluded
USERID 111 identifies SITEID 111, SITEID 112 and SITEID 113 to be excluded
USERID 111 preferences are SITEID's 115, 116 and 117
As SITEID 115 is in NSW this SITEID isn't available, so the algorithm tries preference 2 - SITEID 116
SITEID 116 is in QLD so we have a match of USERID 111 and SITEID 116
Then the Algorithm moves to the next USERID
(If the algorithm couldn't match any SITEID's from the USERID preferences then it would show "NO MATCH")
(Some SITEID don't have a State assigned or aren't in Australia so these can also show "NO MATCH")

Multiple USERID's can match the same SITEID's
Each USERID starts with a full list of SITEID's before exclusions occur

So the end product will show in the last column for each row (Each USERID), something like:
SITEID 111
SITEID 5435
No Match
SITEID 3267
No Match
No Match
SITEID 111

and so on.

I have attached the spreadsheet and I believe the solution is to Reference the STATEEXCLUSIONS against the StateTable (Table 2) as a dictionary, and Reference the SITEEXCLUSIONS against the SITETABLE (Table 1) as a dictionary to Exclude them from the full Site list and then Match the SITEPREFERENCES. I just can't write the syntax correctly.

Cheers

*Note: This isn't listed on any other forums etc