Vlookup function using zip codes

QuestionEdit

QUESTION: Hello - for your help!

I am trying to create a VLOOKUP using zip code and trying to reference a specific zip code ID. I have changed the formatting of the zip codes to custom zipcode and then changed the format to number in order to hold the first zero of the zip codes, but I am still getting #N/A as my result.

My lookup value is a specific zip code, my table array (first column zip code list, second column zip code IDs).

I know that I do not duplicate zip codes in my list.

Again, thank you for your help.

Gayle

ANSWER: When you use custom formatting then the data you see in the cell is not always the ACTUAL data stored in the cell. For example, if you use the Zip Code +4 format and type 123456789 as the zip then 123456789 is what is actually in the cell but you SEE 12345-6789.

So, I suspect, since I have not seen the data, that your values do not match exactly and that is why you are getting the error. You would NOT type 12345-6789 as your lookup value if you have formatted the zip codes in your table using Zip Code +4. You would type 123456789 as the value to lookup.

---------- FOLLOW-UP ----------

QUESTION:

We are not using the +4 on the zip... Only the first 5 digits... For example, what's in the lookup cell is 48239 or 07003 and what's in the array is 48239. The formats are both custom "00000" to keep the zeros on the zips.

The zip code id has both numbers and letters so that column is formatted as general.

Hope this gives you more background on the issue.

AnswerEdit

Without seeing the data and without knowing what cells you are referenecing in the VLOOKUP and what is in those cells then it is real hard for me to say for sure why you are getting the error. I do know that the err means the data can not be found.

Don't know if you are using:

=VLOOKUP("07003, B1:C20, 2, FALSE) or =VLOOKUP(A1, B1:C20, 2, FALSE) and if the latter, what has been typed in A1. You don't give me any of this information. Also, for the zip code of 07003, IF you are using the custom format of 00000, then only 4 digits are stored, which are 7003, and not five (07003). I suspect this still may be your problem.

In the earlier examples, you would need to use

=VLOOKUP("7003, B1:C20, 2, FALSE) or =VLOOKUP(A1, B1:C20, 2, FALSE) where A1 would have 7003