VLOOKUP Zip Code mileage problem

[2Busy]

Ok, I've been working on a formula to calculate straight-line distance between two zip codes.

It's working well, but I am having a problem getting the formula to work correctly when I try to make the zip code a variable. I'm not sure if using a reference for the first argument in the RIGHT function would cause this or if it's a problem somewhere in the LOOKUP formulas.

Here is the logic that works so long as the zip code is manually typed into a cell in column D:

Miles between 2 zip codes (in column E)=

Please Login or Register  to view this content.

(The 1.2 is a raw adjustment to get closer to driving miles)

Where:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

+

Please Login or Register  to view this content.

Please Login or Register  to view this content.

My location addresses are in Column C and include Street, City, State, Zip Code. I would to incorporate logic to pull the zip code from this string so I won't have to manually type the zip code into column D. I can use

Please Login or Register  to view this content.

too pull this but when I have the RIGHT function in cell D instead of the zip code typed in I get different mileage results in column E and I'm not sure why.

I know that ideally you want data to be seperated out as much as possible but these locations are generated in csv's by our enterprise system and are updated constantly. There's no easy way to get the zip code in a cell all of it's own without having to type it in due to foreign locations, string lengths, etc...

Can anyone identify what it is with the RIGHT function that causes my LOOKUPs to function differently? Is there a better way to get the zip code as a variable?

[TMS]

Try changing TRUE to FALSE in each of the VLOOKUPs for an exact match.

Regards

[2Busy]

That doesn't explain why I get different results when using the RIGHT function (which returns the zip code) than when I use the hard-coded zip code.

I'm only using the TRUE argument to prevent errors atm...

[TMS]

So a zip code can approximate to another zip code?

[2Busy]

The zip codes table is sorted. If you've been through a list of zip codes they follow a pattern. 62617 won't be geographically far from 62618. 0 is a mileage return that I will accept. Less than 2hrs transit time is negligible and won't induce layovers.

Edit: I should make a note as to how the OFFSET function is affecting the calculation.

The spreadsheet will show a customer name in column B, location in column C if populated by a VLOOKUP formula. Trucks are seperated by a blank row.

so:

*blank*
Customer 1
Customer 2
Customer 3
*blank*
Customer 1
Customer 2

[TMS]

Can you post an example workbook with a subset of your data to demonstrate what you expect to get and what you actually get?

It'll save a lot of guesswork.

And no, I've never wanted or needed to look at a table of zip codes.

Regards

[2Busy]

Can you post an example workbook with a subset of your data to demonstrate what you expect to get and what you actually get?

It'll save a lot of guesswork.

And no, I've never wanted or needed to look at a table of zip codes.

Regards

Yes sir will do when I get a moment, hectic day and I want to make sure I don't divulge any information that I shouldn't. Thank You!