Trying to Use VLOOKUP to Pull City & State from Zip Code Database

I am trying to use zip code locations to pull city and state data on each zip code. I downloaded a zip code database but the zip codes are in text format in the database. VLOOKUP formula is correct as far as I can tell -- =VLOOKUP(F2,ZipCode!A:D,3,FALSE) -- but I keep getting "Value is not available to the formula or function" error message (#N/A). I believe the issue is that the zip code database has the zip codes in text rather than number format. So, when I try to pull a value from the database compared to my zip code list (which is in number format), Excel does not read the value.

My question is: how can I convert a column of zip codes in text format to number format?

Thanks.

Rather than trying to change them all, you can change the reference to F2 directly in the formula, like this:

=VLOOKUP(F2&"",ZipCode!A:D,3,FALSE)

which will convert the numeric F2 value into a text value.

You might have to use the TEXT function if your zip codes are not pure numbers (e.g. if there are spaces in them).

Hope this helps.

Pete

Your suggestion worked perfectly. Thanks! Please let me know how I can support the forum with a donation. Much appreciated!

Worked perfectly -- thanks!

Please let me know how I can support the forum with a donation.

All contributions to the forum are offered free of charge - none of us get paid, and so do not solicit such rewards.

There are two things you could do, however, to wrap up the thread:

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete