Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Zip Code Match, Return City, State

Posted on 2013-06-26
8
Medium Priority
?
8,672 Views
Last Modified: 2013-06-27
On Sheet1 I have a Zip Code List in Column A.  Sheet2 is a full database of all Zip Code information in the U.S. including City and State info.

I'm trying to get the City and State info from Sheet2, to show up in Sheet1 next to my Zip Code.

So basically I need to search Sheet2 and if it finds a match, take that City and State info and put it next to my Zip Code in Sheet1.  

I'm assuming this is going to be a VLOOKUP formula, but I cannot get it to work.  Right now my VLOOKUP to look up the City info looks like this:

=VLOOKUP(A1,Sheet2!B2:B81837,4)

4 is the column the City info is in.  Am I not referencing it correctly?
0
Comment
Question by:FH_JGoodwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39279779
Assuming your ZIP in Sheet2 is in Col B, and City is in Col D:

=VLOOKUP(A1,Sheet2!B:D,3,FALSE)

You may want to refer to my article on troubleshooting VLOOKUP:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html
0
 

Author Comment

by:FH_JGoodwin
ID: 39279834
I received back some #N/A and some FALSE with your formula (the #N/A would be from bad zip code data, I'm assuming).  

From what I can see, I'm having trouble with the very last part -- referencing column D in Sheet2 and pulling that City name and putting it in my formula cell.

For example, if 62301 is in Sheet1 A1, the formula should search Sheet2's Column B for a match, then return it's corresponding value in Column D, which would be the City Name.

I tried this and it gave me back #NAME?:

=VLOOKUP(A2,Sheet2!B:B,Sheet2!D)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39279941
Please look again at the formula I provided:

=VLOOKUP(A1,Sheet2!B:D,3,FALSE)

And at the article I referred you to:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

The article explains the syntax for VLOOKUP, and also why you are getting #N/A for some items.

If you need more help than that, you need to post a sample file.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Accepted Solution

by:
chunkheatkhoo earned 2000 total points
ID: 39280212
Try using the Match and Index method.  I've attached a sample spreadsheet to this.  The advantage of using Match and Index is that the reference list is Sheet2 does not need to be sorted by the Zip.  Note that I have used Named Ranges in Sheet2 to make the formulas easier to read.
City-StateLookup.xlsx
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39280220
chunkheatkhoo,

It is NOT correct that you must sort that table for VLOOKUP to work.  If you use FALSE for the 4th argument, as I did, it does not matter whether it is sorted.

The article I linked to above explains this very clearly.

Patrick
0
 
LVL 1

Expert Comment

by:chunkheatkhoo
ID: 39280263
matthewspatrick

Yes you are right.  I guess it is just a matter of preference.  However, we do need to be careful that if we omit the last argument, then VLOOKUP would require the list to be sorted.
0
 

Author Closing Comment

by:FH_JGoodwin
ID: 39281302
This worked perfectly.  I went into Insert --> Name and named my columns for reference like you did and it was much easier to create the formula.    I named my columns in my reference database exactly like you did and just copied and pasted the formula in there and BAM the information appeared.  Thanks so much!
0
 

Author Comment

by:FH_JGoodwin
ID: 39281319
Patrick, thanks for the help but I was stuck and had already referenced 15 different articles that had the same information in them that you posted in yours.   I use this site as a last ditch effort to solve a problem that I'm spending too much time on.  Reading over a whole other article on VLOOKUP was not helping me.  chunkheatkhoo posted the exact solution and attached a file for reference.  Hard not to give him all the points.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question