My brain hurts...

[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

 

[Dave R.]

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

 

[Ken Wright]

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.

 

[John Croson]

THANKS! Worked like a charm...

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.