VLOOKUP and Zipcodes with ZEROS

[Utradeshow]

Hi all,

I am Using a zip program to find nearest zip code. Then I use a VLOOKUP to populate cells. I have to convert the result of the zip code program to work with the VLOOKUP. I tried using VALUE function which works on zips with the full 5 digits and doesn't work with the ZERO leading Zipcodes. Then the TEXT function works with the "ZERO" zips and not the regular zip codes???? Any Suggestions?

 

[Utradeshow]

A35 returned TRUE

 

[Utradeshow]

Copy of Rad One Delivery List with Agent (TEST2).xlsm
	G	H	I	J	K	L
6	19446	#VALUE!	#VALUE!	19446	18	Hughes Relocation
7	07004	#VALUE!	#VALUE!	07004	17	TTI Fairfield
1000 locations

Cell Formulas
Range	Formula
G6:G7	G6	=CDXClosestZip(E6,0,'Preferred Agents'!A$2:A$107)
H6:H7	H6	=CDXClosestZip(E6,4,'Preferred Agents'!#REF!)
I6:I7	I6	=CDXClosestZip(E6,8,'Preferred Agents'!#REF!)
J6:J7	J6	=TEXT(G6,"00000")
K6:K7	K6	=CDXDistance(E6,J6)
L6	L6	=VLOOKUP(J6,'Preferred Agents'!A:C,3,FALSE)
L7	L7	=IFERROR(VLOOKUP(J7,'Preferred Agents'!A:C,3,FALSE),"")

Ok, converted everything to text and it seems to be working now!!! Thank you Gentlemen!

 

[Joe4]

OK, that is your problem. You have a mixture of Numbers and Non-Numbers in column A on that sheet.
ALL the data needs to be of the same data type.

Where did this data come from?
How did it get populated?

Here is how you can fix it:
1. Highlight column A
2. Right-click on any cell in column, select Format Cells, then go to to the Number tab and choose "Text"
3. With column A still highlighted, go to the Data menu, and select "Text to Columns"
4. Hit the "Next" button two times until you get to Step 3
5. Select the "Text" radio button
6. Click "Finish"

Now all your values in column A should be Text.
Don't apply the VALUE function to any values at all on your other sheet, and the VLOOKUP formulas should work.

 

[Utradeshow]

I am using a plugin CDXZIPSTREAM to locate nearest zip codes comparing two data sets

 

[Joe4]

Fix column A like I instructed in the last post, and it should resolve your issue.