Excel – How to get Excel to identify a country from city list

microsoft excelmicrosoft-excel-2010worksheet-function

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?

Best Answer

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:

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.

Related Question