G
Guest
I'm running a vlookup on a list of customer accounts to find what store is
closest to the customer by zip codes. However, for every single customer, my
formula is returning the last store number in the lookup array. I have the
vlookup set to true to find the closest match, I made sure all my zip codes
in the customer table were only 5 digits instead of 9 to match the store
master list, and I formatted both columns to number with zero decimal places,
and no luck. I've also sorted the store table in ascending order of zip
codes. Here's a sample of the data from the two files:
Store List (in the sheet it's in columns, but it's huge, so I'm typing it in
list form):
Store num: 78
Store No: READING - Zeswitz
Station: 1053859000
Address: 5550 perkiomen ave
City: reading
State: pa
Zip Code: 19606
Phone: 6105822082
REGION NUM: 5
Cost Center: 011178
Payroll: 078
Region Name: 11
Customer File (same format as above):
Acct No.: Q99600
Contract No.: AA8ZEJ
Customer Name: DAVID E. JONES
City: APO
ST: AE
Zip: 9012
Date: 20050309
Due: 101.52
No.: 51
Type: R
Frozen: F
For each line in the Customer sheet, I get the store information for store
71, which has a zip code of 79936. But most of my search sheet consists of
customers in North Carolina, where the zip codes start with 2. I am very
confused at this point. Any help is appreciated.
Nick
closest to the customer by zip codes. However, for every single customer, my
formula is returning the last store number in the lookup array. I have the
vlookup set to true to find the closest match, I made sure all my zip codes
in the customer table were only 5 digits instead of 9 to match the store
master list, and I formatted both columns to number with zero decimal places,
and no luck. I've also sorted the store table in ascending order of zip
codes. Here's a sample of the data from the two files:
Store List (in the sheet it's in columns, but it's huge, so I'm typing it in
list form):
Store num: 78
Store No: READING - Zeswitz
Station: 1053859000
Address: 5550 perkiomen ave
City: reading
State: pa
Zip Code: 19606
Phone: 6105822082
REGION NUM: 5
Cost Center: 011178
Payroll: 078
Region Name: 11
Customer File (same format as above):
Acct No.: Q99600
Contract No.: AA8ZEJ
Customer Name: DAVID E. JONES
City: APO
ST: AE
Zip: 9012
Date: 20050309
Due: 101.52
No.: 51
Type: R
Frozen: F
For each line in the Customer sheet, I get the store information for store
71, which has a zip code of 79936. But most of my search sheet consists of
customers in North Carolina, where the zip codes start with 2. I am very
confused at this point. Any help is appreciated.
Nick