VLookup Function HELP

[michelle.green0312]

I have an existing spreadsheet with several columns of data. In column O, Ihave about 6200 zip codes. In column AD, I have a list of over 20,000 zip codes corresponding to there Zone in column AE. I wrote a vlookup function to find the zip code in column AD from the one in Column O and write the Zone.

=VLOOKUP(O2,AD3:AE36237,2,FALSE)

It keeps returning #N/A. I know some of the numbers are matchings, but it is not returning the values. Any help on this?

 

[Claus Busch]

Hi Michelle,

Am Mon, 3 Feb 2014 06:09:51 -0800 (PST) schrieb
(e-mail address removed):

I have an existing spreadsheet with several columns of data. In column O, I have about 6200 zip codes. In column AD, I have a list of over 20,000 zip codes corresponding to there Zone in column AE. I wrote a vlookup function to find the zip code in column AD from the one in Column O and write the Zone.

=VLOOKUP(O2,AD3:AE36237,2,FALSE)

It keeps returning #N/A. I know some of the numbers are matchings, but it is not returning the values. Any help on this?

are the zip codes different on the sheets? In one sheet custom formatted
numbers, in the other sheet text formatted? Check your data for not
visible characters.


Regards
Claus B.

 

[michelle.green0312]

Hi Michelle,



Am Mon, 3 Feb 2014 06:09:51 -0800 (PST) schrieb

(e-mail address removed):






are the zip codes different on the sheets? In one sheet custom formatted

numbers, in the other sheet text formatted? Check your data for not

visible characters.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

The zips are formatted as general.

ZIP _VALUE ZONE_NAME
V2S8K1 V5B3E1 CAN BC - 09 #N/A =VLOOKUP(A2,$B$2:$C$36236,2,FALSE)
T5S2Y2 V5B3E2 CAN BC - 09 #N/A
T5S2Y2 V5B3E3 CAN BC - 09 #N/A
T5S2Y2 V5B3E5 CAN BC - 09 #N/A
T5S2Y2 V5B3E6 CAN BC - 09 #N/A
T5S2Y2 V5B3E7 CAN BC - 09 #N/A
T5S2Y2 V5B3E8 CAN BC - 09 #N/A
T5S2Y2 V5B3E9 CAN BC - 09 #N/A
T5S2Y2 V5B3G1 CAN BC - 09 #N/A
T4B3G5 V5B3G2 CAN BC - 09 #N/A
T4A0H5 V5B3G3 CAN BC - 09 #N/A
T4A0H5 V5B3G4 CAN BC - 09 #N/A
T4B3G5 V5B3G5 CAN BC - 09 #N/A
T4A0H5 V5B3G6 CAN BC - 09 #N/A
T4A0C4 V5B3G8 CAN BC - 09 #N/A
L1S7G1 V5B3G9 CAN BC - 09 #N/A
L1S7J4 V5B3H1 CAN BC - 09 #N/A
L1S7G1 V5B3H2 CAN BC - 09 #N/A
L1S7J4 V5B3H3 CAN BC - 09 #N/A
L1S7J4 V5B3H4 CAN BC - 09 #N/A
L1S7G1 V5B3H5 CAN BC - 09 #N/A
L1S7G1 V5B3H6 CAN BC - 09 #N/A
L9R1K1 V5B3H7 CAN BC - 09 #N/A
L9R1B5 V5B3H8 CAN BC - 09 #N/A
L9R1K1 V5B3H9 CAN BC - 09 #N/A
R0G0B0 V5B3J1 CAN BC - 09 #N/A
R0G0B0 V5B3J2 CAN BC - 09 #N/A
R0G0B0 V5B3J3 CAN BC - 09 #N/A
R0G0B0 V5B3J4 CAN BC - 09 #N/A
R0G0B0 V5B3J5 CAN BC - 09 #N/A
R0G0B0 V5B3J6 CAN BC - 09 #N/A
N9V1Y3 V5B3J9 CAN BC - 09 #N/A
N9V2J8 V5B3K1 CAN BC - 09 #N/A
N9V2J8 V5B3K2 CAN BC - 09 #N/A
N9V2J8 V5B3K3 CAN BC - 09 #N/A
N9V2J8 V5B3K4 CAN BC - 09 #N/A

 

[Claus Busch]

Hi Michelle,

Am Mon, 3 Feb 2014 06:43:36 -0800 (PST) schrieb
(e-mail address removed):

The zips are formatted as general.

in your example data there is no match.
Check it with :
=COUNTIF($B$2:$B$36236,A2)
and copy down. You get zero as result.


Regards
Claus B.

 

[michelle.green0312]

Hi Michelle,



Am Mon, 3 Feb 2014 06:43:36 -0800 (PST) schrieb

(e-mail address removed):






in your example data there is no match.

Check it with :

=COUNTIF($B$2:$B$36236,A2)

and copy down. You get zero as result.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Sorry, I took an extraction from the data. Is there a way I can attach the spreadsheet with all the data?

 

[Don Guillett]

I have an existing spreadsheet with several columns of data. In column O,I have about 6200 zip codes. In column AD, I have a list of over 20,000 zip codes corresponding to there Zone in column AE. I wrote a vlookup function to find the zip code in column AD from the one in Column O and write the Zone.



=VLOOKUP(O2,AD3:AE36237,2,FALSE)



It keeps returning #N/A. I know some of the numbers are matchings, but itis not returning the values. Any help on this?

You can send direct to (e-mail address removed)

 

[joeu2004]

The zips are formatted as general.

ZIP _VALUE ZONE_NAME
V2S8K1 V5B3E1 CAN BC - 09 #N/A

The format of the cell does not matter. We can always enter text into a
cell with a numeric format. Obviously, a zip code like V2S8K1 is text, not
numeric.

If all the zip codes start with a letter, all of them are text.

However, there might be leading and trailing spaces -- and even non-breaking
spaces (HTML &nbsp), if you copy-and-pasted data from a webpage.

Try the following:

1. In an unused column, enter the following formula starting in row 2 and
copying down through row 36236:
=TRIM(SUBSTITUTE(B2,CHAR(160),""))

2. Copy that column of results and use paste-special-value to replace the
values in B2:B36236.

3. Do the same with the data in column A starting with A2.

4. When done, delete the TRIM formulas in the previously-unused column.

If that does not remedy the problem, and if the Excel file does not contain
private data, you can upload it to a file-sharing website. The following is
a list of some free file-sharing websites; or use your own. Then post the
URL of the "shared" file in a response here.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

 

[Don Guillett]

I have an existing spreadsheet with several columns of data. In column O,I have about 6200 zip codes. In column AD, I have a list of over 20,000 zip codes corresponding to there Zone in column AE. I wrote a vlookup function to find the zip code in column AD from the one in Column O and write the Zone.



=VLOOKUP(O2,AD3:AE36237,2,FALSE)



It keeps returning #N/A. I know some of the numbers are matchings, but itis not returning the values. Any help on this?

SOLVED I saw the file and provided an answer to use trim(a2). Worked just fine