RE: vlookup returns last value in array

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
.

Relevant Pages

  • RE: vlookup returns last value in array
    ... "Nicholas Scarpinato" wrote: ... closest to the customer by zip codes. ... However, for every single customer, my ... formula is returning the last store number in the lookup array. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Store Locator
    ... > I'm trying to create a simple store locator for my website using an Access ... A customer would enter a zip code and the results would ... I would like to list store withing +/- 50 zip ... it would return stores having codes ...
    (microsoft.public.frontpage.client)
  • Store Locator
    ... I'm trying to create a simple store locator for my website using an Access ... A customer would enter a zip code and the results would list ... I would like to list store withing +/- 50 zip codes ...
    (microsoft.public.frontpage.client)
  • Re: report on customer rage
    ... >> verbatim FYI for those with customer service nightmares (and I ... >> As shoppers head into the holiday shopping season, ... >> A shopper at a Michael's craft store is behind a man doing a price ... who rebuilt yur kombi for ya? ...
    (alt.fashion)
  • Re: TSWLTH revisited
    ... Not the company; not the store manager. ... to JoAnn Fab. ... That, of course, isn't true, and has been replaced by "The customer ... nobody's mind is going to be changed about JoAnn/s Fabrics by ...
    (alt.sewing)