Vlookup List errors

  • I am running excel office XP and have a basic program to lookup my customers addresses. This is the code that I am using =IF(COUNTIF(AllCompanies,$A23)<>0,VLOOKUP('QUOTE TEMP'!$A23,AllData,2,FALSE),"") I am using a second hidden sheet as the "list" . A23 will give the customer # to lookup. It works great but I have approx 1500 customers and the program refuses to look past line #292 on the list. I realize that excel looks into ascending order and a lower value than your lookup value would result in a error. I have tried to sort and have even gone over each customer # one by one to look for typos but all customers above line 293 work fine. I even cut and pasted a few customers lower down the list to rows above 293 and they work fine. Im pulling my hair out trying to figure it out. Any help is greatly appreciated. Merry Christmas to all!!!!

  • Re: Vlookup List errors

    Not sure if I understand your problem properly, but I had sometthing similar happen. I traced the problem to the data range. Does your data range extend only to line 293? That could be one reason it stops at that line.

    Joe Walsh

  • Re: Vlookup List errors

    basically I have 1500 customerst and thier address that looks like this

    A1 B1
    1 Cust # Name +Address
    2 100 ABC Co. 300 oak street boston
    3 101 ACME 200 state street newark
    4 102 Smith Company 100 elm street
    5 103 Joes Plumbing 50 pine street

    The problem is I have over 1500 of them but the above macro will only read up to customer #293. I have tried everything. I have cut and pasted customers from way down the list and tested them above A293 and they work. All data below A293 is not being "Seen" I geuss. ????? :thanx:

  • Re: Vlookup List errors

    Are you sure your named range: "AllData" captures all the 1500 rows? You might want to use Dynamic Named Ranges in order to make sure you always capture the data if you are adding data to that list.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!