software

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

My guess is that the zip codes in your addresses are text, while the lookup
table has them as numbers. You can try

=vlookup(1*zipcode,table, col)

to confirm it. If it's the other way around - looking up numbers against a
table of text ZIPs (it shouldn't be, else you would not be getting the last
entry in the table), you can try

=vlookup(zipcodeamp;quot;quot;,table, col)

It iis important to note that formatting a text value as a number does NOT
change it to a number.quot;Nicholas Scarpinatoquot; wrote:

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

I thought about that, because my lookup sheet came from an Access table. I
went in and manually deleted out all the leading ' to make sure the values
would show as numbers in both tables, with no change whatsoever.

quot;Duke Careyquot; wrote:

gt; My guess is that the zip codes in your addresses are text, while the lookup
gt; table has them as numbers. You can try
gt;
gt; =vlookup(1*zipcode,table, col)
gt;
gt; to confirm it. If it's the other way around - looking up numbers against a
gt; table of text ZIPs (it shouldn't be, else you would not be getting the last
gt; entry in the table), you can try
gt;
gt; =vlookup(zipcodeamp;quot;quot;,table, col)
gt;
gt; It iis important to note that formatting a text value as a number does NOT
gt; change it to a number.
gt;
gt;
gt; quot;Nicholas Scarpinatoquot; wrote:
gt;
gt; gt; I'm running a vlookup on a list of customer accounts to find what store is
gt; gt; closest to the customer by zip codes. However, for every single customer, my
gt; gt; formula is returning the last store number in the lookup array. I have the
gt; gt; vlookup set to true to find the closest match, I made sure all my zip codes
gt; gt; in the customer table were only 5 digits instead of 9 to match the store
gt; gt; master list, and I formatted both columns to number with zero decimal places,
gt; gt; and no luck. I've also sorted the store table in ascending order of zip
gt; gt; codes. Here's a sample of the data from the two files:
gt; gt;
gt; gt; Store List (in the sheet it's in columns, but it's huge, so I'm typing it in
gt; gt; list form):
gt; gt; Store num: 78
gt; gt; Store No: READING - Zeswitz
gt; gt; Station: 1053859000
gt; gt; Address: 5550 perkiomen ave
gt; gt; City: reading
gt; gt; State: pa
gt; gt; Zip Code: 19606
gt; gt; Phone: 6105822082
gt; gt; REGION NUM: 5
gt; gt; Cost Center: 011178
gt; gt; Payroll: 078
gt; gt; Region Name: 11
gt; gt;
gt; gt; Customer File (same format as above):
gt; gt; Acct No.: Q99600
gt; gt; Contract No.: AA8ZEJ
gt; gt; Customer Name: DAVID E. JONES
gt; gt; City: APO
gt; gt; ST: AE
gt; gt; Zip: 9012
gt; gt; Date: 20050309
gt; gt; Due: 101.52
gt; gt; No.: 51
gt; gt; Type: R
gt; gt; Frozen: F
gt; gt;
gt; gt; For each line in the Customer sheet, I get the store information for store
gt; gt; 71, which has a zip code of 79936. But most of my search sheet consists of
gt; gt; customers in North Carolina, where the zip codes start with 2. I am very
gt; gt; confused at this point. Any help is appreciated.
gt; gt;
gt; gt;
gt; gt; Nick

Did you try either of the formulas? What happened?

quot;Nicholas Scarpinatoquot; wrote:

gt; I thought about that, because my lookup sheet came from an Access table. I
gt; went in and manually deleted out all the leading ' to make sure the values
gt; would show as numbers in both tables, with no change whatsoever.
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; My guess is that the zip codes in your addresses are text, while the lookup
gt; gt; table has them as numbers. You can try
gt; gt;
gt; gt; =vlookup(1*zipcode,table, col)
gt; gt;
gt; gt; to confirm it. If it's the other way around - looking up numbers against a
gt; gt; table of text ZIPs (it shouldn't be, else you would not be getting the last
gt; gt; entry in the table), you can try
gt; gt;
gt; gt; =vlookup(zipcodeamp;quot;quot;,table, col)
gt; gt;
gt; gt; It iis important to note that formatting a text value as a number does NOT
gt; gt; change it to a number.
gt; gt;
gt; gt;
gt; gt; quot;Nicholas Scarpinatoquot; wrote:
gt; gt;
gt; gt; gt; I'm running a vlookup on a list of customer accounts to find what store is
gt; gt; gt; closest to the customer by zip codes. However, for every single customer, my
gt; gt; gt; formula is returning the last store number in the lookup array. I have the
gt; gt; gt; vlookup set to true to find the closest match, I made sure all my zip codes
gt; gt; gt; in the customer table were only 5 digits instead of 9 to match the store
gt; gt; gt; master list, and I formatted both columns to number with zero decimal places,
gt; gt; gt; and no luck. I've also sorted the store table in ascending order of zip
gt; gt; gt; codes. Here's a sample of the data from the two files:
gt; gt; gt;
gt; gt; gt; Store List (in the sheet it's in columns, but it's huge, so I'm typing it in
gt; gt; gt; list form):
gt; gt; gt; Store num: 78
gt; gt; gt; Store No: READING - Zeswitz
gt; gt; gt; Station: 1053859000
gt; gt; gt; Address: 5550 perkiomen ave
gt; gt; gt; City: reading
gt; gt; gt; State: pa
gt; gt; gt; Zip Code: 19606
gt; gt; gt; Phone: 6105822082
gt; gt; gt; REGION NUM: 5
gt; gt; gt; Cost Center: 011178
gt; gt; gt; Payroll: 078
gt; gt; gt; Region Name: 11
gt; gt; gt;
gt; gt; gt; Customer File (same format as above):
gt; gt; gt; Acct No.: Q99600
gt; gt; gt; Contract No.: AA8ZEJ
gt; gt; gt; Customer Name: DAVID E. JONES
gt; gt; gt; City: APO
gt; gt; gt; ST: AE
gt; gt; gt; Zip: 9012
gt; gt; gt; Date: 20050309
gt; gt; gt; Due: 101.52
gt; gt; gt; No.: 51
gt; gt; gt; Type: R
gt; gt; gt; Frozen: F
gt; gt; gt;
gt; gt; gt; For each line in the Customer sheet, I get the store information for store
gt; gt; gt; 71, which has a zip code of 79936. But most of my search sheet consists of
gt; gt; gt; customers in North Carolina, where the zip codes start with 2. I am very
gt; gt; gt; confused at this point. Any help is appreciated.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Nick