Mutiple dependent drop down list with a fallback/default list on error.

I have been searching and so far been unable to find a solution.

I have site lists lots of them in a multiple dependent drop downs two of, and i need to add another drop down based on selections however the third drop down fields need only to be a few.

i.e.
State Based (7 selections) -> Street Address (minimum of 3, up to 20) -> Internal location (only need 3 different lists)

State
ACT
NSW
NT
QLD
TAS
VIC
WA

Each state has multiple addresses which has been defined in a list and set up as a dependent drop down and works without issue.

=INDIRECT($A$1)

Now here's the bit i need, when I select an address there are only 3 lists I need to select from, two of them are tied to a street address with a named list and the third list = AllOther.

This works without a problem however I would like / need it to default to a named list when it does not match either of the first two named lists. This way I don't have to make 100 or so other named lists.

I have tried searching and even tried working it out myself, but after many hours of frustration I am stumped.

I don't even know if it can be done or if I am on the right track, I would have thought one of the following would have worked. (in all cases with or without " around AllOther)

=IFERROR(INDIRECT(SUBSTITUTE(SUBSTITUTE($A$2," ",""),"-","")),"AllOther")
=IFERROR(INDIRECT(SUBSTITUTE(SUBSTITUTE($A$2," ",""),"-","")),INDIRECT("AllOther"))

=INDIRECT(IFERROR(SUBSTITUTE(SUBSTITUTE($A$2," ",""),"-",""),"AllOther"))
=INDIRECT(IFERROR(SUBSTITUTE(SUBSTITUTE($A$2," ",""),"-",""),INDIRECT("AllOther")))

Is this possible or am I on a goose chase.... and most importantly can anyone point me in the right direction.

Any help will be very much appreciated.

Dave

Thanks for this noboffinme,

Multi Dependent Drop Down with Default List.xlsx

Appreciate u looking.