Script lookup/match field for post codes (zip codes)
I have a table (postcodes) with over 450 records (suburbs) which contains the following fields -
The table currently contains records relating to suburbs in one state - QLD. Everything other than QLD (QLD has postcodes with 4***) will be assigned to an 'interstate' campus. There are 5 campuses.
I have a contacts layout which contains address details (sourced from the CONTACTS table) and campus. Suburb, state and postcode are split into individual fields.
I need to assign each address a campus based on it's postcode, in a script form.
I need the script to say, if the postcode does not start with 4, to assign the campus as 'Interstate', for everything else, lookup the postcodes table and match the postcode in the address to the postcode in the Postcodes table and return the campus to the contacts layout.
I have not had any luck and would appreciate it if someone can point me in the right direction please. Thankyou.