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

[greyscale]

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

[dredwolf]

Well, I was thinking we could use VLOOKUP, but that only returns Split/Metro/#N/A (in your example, postcode 4999 does not exist in the list, so the #N/A error, How do you want to handle these?)
so my main question is...how do we dertermine if its a Rural or Metro postcode? (ie--what makes a split a rural or a metro?)

[greyscale]

Hi Dredwolf. Thanks for your reply. We only have postcode listings for SPLIT and METRO, so any postcode that is not on the list is considered Rural. Where a Postcode is listed as SPLIT, we then have to look at the name of the suburb within that postcode. This is where I am coming unstuck because the data is not entered in a standard way. The Suburb name is always just before "QLD" in the address field but can be upper case or lower case, can be separated from QLD by a comma or a space, and can be two words. I need to be able to somehow search the Address cells for any of the values to the right of the SPLIT postcodes. The suburbs on my sample sheet are:
"Ormeau Hills" - which is a split postcode and suburb name appears on the list so is therefore METRO
"Bundaberg West" - which is a split postcode and suburb name appears on the list so is therefore METRO
"Slade Harbour" - which is a split postcode BUT DOES NOT appear on the suburb list so is therefore Rural
"Hammond" - not on the postcode list at all and is therefore Rural
"Brisbane" - on the list as METRO

Does that make sense?

[dredwolf]

Okay, I think I see what you are getting at, give me a bit to work on this and we'll see how well it works..

EDIT-
1 more question...is this expected to run in a Excell 2003 sheet (as per your profile) or a 2007+ sheet (as per your sample) ?

[dredwolf]

Well, without an answer, I am going to suggest this, in C2:

Please Login or Register  to view this content.

Drag Down

Hope this helps

EDIT-
See attached

[dredwolf]

Sorry, got lost a bit, meant this Array Formula ( Cntrl+Shift+Enter, not just Enter) in C2:

Please Login or Register  to view this content.

(same formula, just needed to be array entered)
Drag down

See attached

EDIT -
NOTE - this can still return false positives.. to completely avoid the possibility, you need BETTER source data

[greyscale]

Hi Dredwolf. I'm so sorry for late reply. I viewed your earlier reply through email and didn't realise I wouldn't get another email notification until I logged back into the site. Thanks for looking into it. I will test this out and let you know.