How to create a vlookup to search a zip and add the city

[emma313823]

I'm having a difficult time trying to figure this out. I've used vlookups before but can't seem to figure this out or what I'm doing wrong.

I have data I use each month and often the source data may not have the city or the zip. I have used a vlookup to find the city name and tag it in a new column and then I can filter on that tag. I want to do a vlookup on the zip codes and if a zip is found return the city name to a new column.

Can someone share how I can do this and make it work? I have the zip in column A and the city in column B. I've removed duplicate zips and have it sorted numerically by the zip, but every time I run the vlookup i get NA in every cell.

Thanks for you help
Emma

 

[Joe4]

Note that VLOOKUPs are very particular. Things like Zip Codes can often cause problems, if one set is formatted as Text, and the other is formatted as Numbers.
In VLOOKUPs, you can only compare numbers to numbers, and text to text. So you may need to clean up one of your data sets a little.

If that is not the problem, it would be a big help to get a visual example of your data. You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[emma313823]

let me try that...had not thought of the format on both files mostly because my city one worked fine. Let me try that and if i have issues...i will definitely send an example. thanks so much for the quick response.

Emma

 

[Joe4]

City would work fine, as it would always be a Text-to-Text comparison.
Number can often be problematic, especially things like Social Security Numbers, other ID numbers, Zip Codes, and Phone Numbers, as they could be entered either as Numbers (sometimes with Custom Formatting), or Numbers entered as Text. Usually a dead giveaway is if one list is left-justified and one isn't (by default, Text is left-justified in Excel, and Numerical entries are right-justified, though that can be overridden).

 

[phoenixdown]

I use =isnum() and =istext() to see whether excel is treating the cell as a # or as text.

If I am lazy, I will multiple the cell by 1 to get a text to turn into a number.

=vlookup((A2*1),range,2,false)

 

[Joe4]

I use =isnum() and =istext() to see whether excel is treating the cell as a # or as text.

I assume you mean IsNumber, as isnum is not a valid Excel function.
You are correct, that is one way to check.

Sometimes things like extra spaces also cause issues. If you come across an issue where you see a match, and it doesn't appear to be working, see if Excel thinks they are equal.
For example, let's say that it looks like cell A1 matches cell E100. Then enter this function in any available cell:
=A1=E100
If that returns FALSE, then regardless of what it looks like at first glance, those cells are NOT the same.
So you could try using the IsNumber and IsText functions on it. I also sometimes use the LEN function to see if there lengths are the same (that checks for extra spaces or special characters).