Build a table of Continent, Country, County, State, and City

I am looking to build a table of Continent > Country > County / State > City so that when entering a City name into a cell, Excel autocompletes the corresponding row with the accompanying Continent - Country - County / State data. I suspect this would be too imprecise with (e.g. Perth in Scotland and Perth in Australia) so possibly enter Continent > Country and then city for the county / state to be autocompleted but either way this is an enormous data set possibly using index(match( or something cleverer coding somewhat slightly beyond my technical capability.

I am open to any suggestion or proposals which anyone may have including if they had a data table already build? I have a list of Continent > Countries and ~10 countries with County / State > City already build which is build on indirect() drop downs but is not the most efficient way and loses a lot of data for location where i haven't build the county / State > City data.

Any/all suggestion welcome including if thoughts are this is too ambitious for excel to manage? Thanks for any help in advance.

Kev,

Sorry for the slow response, have been hectic at work and finally got back to this.


How are you defining "city" - I haven't set a definition, if a parameter existed it would be major economical cities globally, you mentioned 4000 and i imagine that is well within the count i am looking at, in fact i suspect largest 500 cities in the world would be sufficient.
How are you planning to use this database? - Excel is a sole access file (i.e. myself) which is then reporting out trend and patterns identified.
How often is access required and by how many people? - Accessed daily but only by myself.

What i lack if the Continent > Country > State/County/Governance/City structure for the hypothetical 500 and the excel code to simply enter the City name for the pre-dependent State/County/Governance > Country > Continent to auto-populate.

Any thoughts or suggestions welcome. I have a feeling i am asking a little too much here but hope i'm wrong.

if you google "table (or list) of cities and countries" you get multiple free and paid examples of databases with cities.

if it is only 500 cities I would problably take a rainy afternoon (or day) to find the information online,
wikipedia also seems a good place to start..
https://en.wikipedia.org/wiki/Lists_...ies_by_country
or
https://en.wikipedia.org/wiki/Lists_of_cities

I have attached a spreadsheet which may help demonstrate what i'm hoping to achieve

Essentially i am looking for a manual text entry of a city name into H2 where excel will then look up the accompanying Country / State, Country and Continent. I may be structuring this data and tabling poorly so very open to thoughts or suggestions.

Thank you for any help in advance.

Most of countries/cities on Wiki are not well organized (non-tabular form) so you'll need a lot of work to organize it all correctly

here is an example of EU countries with cities and provinces (counties/regions/districts ... whatever) but not all.

to see how it works you'll need fast internet connection and PowerQuery add-in