How to perform multiple searches across rows and columns in one statement

Hi, I have a large spreadsheet containing thousands of addresses in a non standard format (e.g. sometimes there is a comma separating town from state, othertimes not). I have no control over this spreadsheet and am simply sent a copy of it each month so cannot make any underlying changes to it. I need to lookup the postcode on these addresses and determine whether the postcode is Rural or Metro. Challenge is, that some postcodes are split and an additional lookup is needed to see if the suburb name appears in the suburb list.

Can anyone help me with a calculation that will return either Rural or Metro on the attached document? I have a couple of sample address on the doc (cannot use live version due to privacy). I do have control over the Postcode document so can structure it anyway that is required.

Any help greatly appreciated.

Thanks