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.
Best Answer
You can do this easily with a pivot table.
First, select your data. Then, from the
Insert
ribbon, choosePivot Table
. In the dialog that pops up, you will need to tell Excel where to place your pivot table. By default, it is on a new sheet.After clicking OK, you will see something like this:
In the PivotTable Field List, click and drag the field name you want to summarize ('Category' in my example) to the Row Labels box. Then, click and drag the same field name into the Values box. When you've done this you should see this:
As you can see, this gives you the table you requested.
More information about pivot tables in Excel