vlookup returns last value in array

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(zipcode&"",table, col)

It iis important to note that formatting a text value as a number does NOT
change it to a number.


"Nicholas Scarpinato" wrote:

> 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

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.

"Duke Carey" wrote:

> 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(zipcode&"",table, col)
>
> It iis important to note that formatting a text value as a number does NOT
> change it to a number.
>
>
> "Nicholas Scarpinato" wrote:
>
> > 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

Did you try either of the formulas? What happened?

"Nicholas Scarpinato" wrote:

> 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.
>
> "Duke Carey" wrote:
>
> > 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(zipcode&"",table, col)
> >
> > It iis important to note that formatting a text value as a number does NOT
> > change it to a number.
> >
> >
> > "Nicholas Scarpinato" wrote:
> >
> > > 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