My brain hurts...

  • Thread starter Thread starter John Croson
  • Start date Start date
J

John Croson

I have two worksheets, one is zipcode:

A B C
data zip state
wi53202 =RIGHT(A2,5) =LEFT(A2,2)

giving me:

A B C
wi53202 53202 WI


The other worksheet is Data:

A B
Zip State
53202 =VLOOKUP(A2,ZipCodes!$B$2:$C$33234,2,FALSE)

Simple enough...NOT! I get #N/A....

I'm a noob at this, so be gentle...

JC
 
Is it sorted right? It needs to be sorted to work right- check Excel help.

No it doesn't :-) - You can use the optional 4th argument of FALSE or 0 to
avoid having to sort the data - Listed in the help for the function.

The problem is that you are searching for a numeric field in what are now text
fields. You can either:-

change your =RIGHT(A2,5) to =--RIGHT(A2,5), which will coerce the result of the
RIGHT function to a numeric value, as long as there are no text characters
anywhere in there,

or

change your =VLOOKUP(A2,ZipCodes!$B$2:$C$33234,2,FALSE) to
=VLOOKUP(TEXT(A2,"@"),ZipCodes!$B$2:$C$33234,2,FALSE) which will change the
numeric field you are looking for to a text field so that it matches your table
of data.
 
THANKS! Worked like a charm...



Ken Wright said:
No it doesn't :-) - You can use the optional 4th argument of FALSE or 0 to
avoid having to sort the data - Listed in the help for the function.


The problem is that you are searching for a numeric field in what are now text
fields. You can either:-

change your =RIGHT(A2,5) to =--RIGHT(A2,5), which will coerce the result of the
RIGHT function to a numeric value, as long as there are no text characters
anywhere in there,

or

change your =VLOOKUP(A2,ZipCodes!$B$2:$C$33234,2,FALSE) to
=VLOOKUP(TEXT(A2,"@"),ZipCodes!$B$2:$C$33234,2,FALSE) which will change the
numeric field you are looking for to a text field so that it matches your table
of data.
Click to expand...
 
Back
Top