How do I get Excel to identify a country from city list?

Question

I have a list of data with many columns.

In one column - let's say C, there is a list of cities/places. There are multiple entries, i.e. one city pops up loads of times in a column in different rows.

I have, separately, a list which tells me what cities/places align with which countries.

I would like to set Excel the task of using the Column C data to populate an empty column with the correct country relating to that city/place. Say column D for arguments sake...

How can I do this?

Solution

I don't like VLOOKUP for a few reasons. I would recommend using a combination of INDEX and MATCH for your solution. As warned about in the comments, the simple solution relies on city names only appearing in your list once.

Let's say that Column C has a city name and you want Column D to show you the country (or state or official motto or most popular hair color or whatever). Let's also say that you have a table in another worksheet called CityLookup with Column A & B being the city and country names, respectively. In that case, you could use the following formula to pull in data:
=INDEX(CityLookup!$B:$B,MATCH($C1,CityLookup!$A:$A,0))

Here's how it works:
1) INDEX takes in an array of data and spits out the Nth value in that array
2) MATCH looks for a certain value in an array of data and spits out where it's found (the 3rd parameter being 0 means that it looks for an exact match)
3) Put it together and you get a little story: (indulge me)
Index says, "Hello there. What array would you like me to use?... Ah, Column B from the tab CityLookup? Splendid. That has a big giant list of countries in it. And, ah, which record would you like?"
Match jumps in and says, "Don't worry, Index, I'll look that up for you. So I'm going to find the value in C1? Alright, that's a city name. Where should I look? Of course, right! I should look in Column A in the CityLookup tab. That's a big list of cities. And an exact match, right? OK, I've found the first exact match. There could be more but I'm going to stop here. Index, you should pull out record N."
Index replies, "Record N, you say? Fantastic. Alright, User, here's record N from that big list of countries you gave me. I do hope it serves you well."

I know that's silly, but I hope it makes sense. You can get fancier than use by using tables - which I'm rather a fan of - but I was trying to give a simple example. If you have multiple instances of cities it will be more complex as Jon Story already mentioned. In that case, you'd have to find some unique way of identifying which city you want. For instance, you could require that they put in city and state / township / county / province / canton / whatever your part of the world calls them and then do a match using both those parameters.

Comments

[Jon Story]

Take a look at VLookup if you haven't already. Assuming a city is only ever in one country, it shouldn't be too complext. If you have to handle things like Lancaster, Cumbria, UK and Lancaster, PN, USA, however, it's going to be much more complex.

[Gary's Student]

see: superuser.com/questions/863751/…

[TheEngineer]

By the way @JonStory, Pennsylvania is abbreviated PA. Sorry, just being nitpicky about my state :)

[Jon Story]

If you steal our city names, we get to abbreviate your state names however we like ;)

[Cas101]

From the VLookup examples I've seen they don't readily lend themselves to this? could be missing something as my natural inclination was that VLookup would do it. Is there an example you could point me to? thanks for the advice so far!

[Cas101]

Got it. Found a good Vlookup walkthrough.