Vlookup problem with Access Query import into Excel 2000 "Neophyte" <wleecoleman[ at ]ev1.net> 17.07.2006 00:03:47 I have exported from an Order Entry program to comma delimited text file customer purchases. The file has more entries than Excel can handle on one worksheet. So, I imported the file data into Access (107,000+entries). I then used Excel's "Query Database" feature to import customer level purchases into 4 worksheets. The Order Entry program didn't export customer names, only customer numbers. So, I exported the customer info to another text file and imported it into the Excel Workbook, only 451 entries . The problem is when I did a vlookup function to retrieve the customer name into the worksheet with the customer number, it works when I go into a customer number cell with "F2" and exit. Otherwise, I get the #N/A error because I have it set to false. My formula is =VLOOKUP(A2,Customers!$A$2:$C$415,3,FALSE) in the invoice worksheets. I am using Office 2000. What do I need to do to make things work? I know this hard to follow and trying to explain it is not easy but any help is greatly appreciated. I know more about Excel than Access and that isn't saying much! Thank you very much. Lee

Re: Vlookup problem with Access Query import into Excel 2000 Max 17.07.2006 01:04:02 [Quoted Text] > ... it works when I go into a customer number cell with "F2" and exit. > Otherwise, I get the #N/A error because I > have it set to false. My formula is > =VLOOKUP(A2,Customers!$A$2:$C$415,3,FALSE) > What do I need to do to make things work? As a first guess, try adding a zero to the lookup value, viz use instead: =VLOOKUP(A2+0,Customers!$A$2:$C$415,3,FALSE) Adding a zero is just one way of coercing the text numbers in col A to a real numbers to enable correct matching with the lookup col A in Customers -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Neophyte" wrote: > I have exported from an Order Entry program to comma delimited text file > customer purchases. The file has more entries than Excel can handle on one > worksheet. So, I imported the file data into Access (107,000+entries). I > then used Excel's "Query Database" feature to import customer level > purchases into 4 worksheets. The Order Entry program didn't export customer > names, only customer numbers. So, I exported the customer info to another > text file and imported it into the Excel Workbook, only 451 entries . The > problem is when I did a vlookup function to retrieve the customer name into > the worksheet with the customer number, it works when I go into a customer > number cell with "F2" and exit. Otherwise, I get the #N/A error because I > have it set to false. My formula is > =VLOOKUP(A2,Customers!$A$2:$C$415,3,FALSE) in the invoice worksheets. I am > using Office 2000. > What do I need to do to make things work? I know this hard to follow and > trying to explain it is not easy but any help is greatly appreciated. I know > more about Excel than Access and that isn't saying much! > Thank you very much. > Lee

Re: Vlookup problem with Access Query import into Excel 2000 "CLR" <croberts[ at ]tampabay.rr.com> 17.07.2006 01:05:27 Maybe the two items, the value you are looking up and the lookup table are not actually of the same data type.........one may look like numbers but actually be text so the lookup will not find it.........I've had this problem many times before...... hth Vaya con Dios, Chuck, CABGx3 "Neophyte" <wleecoleman[ at ]ev1.net> wrote in message news:ev1Y1RTqGHA.2232[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > I have exported from an Order Entry program to comma delimited text file > customer purchases. The file has more entries than Excel can handle on one > worksheet. So, I imported the file data into Access (107,000+entries). I > then used Excel's "Query Database" feature to import customer level > purchases into 4 worksheets. The Order Entry program didn't export customer > names, only customer numbers. So, I exported the customer info to another > text file and imported it into the Excel Workbook, only 451 entries . The > problem is when I did a vlookup function to retrieve the customer name into > the worksheet with the customer number, it works when I go into a customer > number cell with "F2" and exit. Otherwise, I get the #N/A error because I > have it set to false. My formula is > =VLOOKUP(A2,Customers!$A$2:$C$415,3,FALSE) in the invoice worksheets. I am > using Office 2000. > What do I need to do to make things work? I know this hard to follow and > trying to explain it is not easy but any help is greatly appreciated. I know > more about Excel than Access and that isn't saying much! > Thank you very much. > Lee > >

Re: Vlookup problem with Access Query import into Excel 2000 "Neophyte" <wleecoleman[ at ]ev1.net> 17.07.2006 02:03:18 That did the trick! I forgot to mention about trying Trim, then Clean, then Cell Format to number and none of them working. I wish that I could be updated with new info as easily as the programs but then I wouldn't have the chance to say thank you very much. Lee "Max" <demechanik[ at ]yahoo.com> wrote in message news:D2CF2574-BDBA-4016-9E27-09D7E3ADF00B[ at ]microsoft.com... [Quoted Text] >> ... it works when I go into a customer number cell with "F2" and exit. >> Otherwise, I get the #N/A error because I >> have it set to false. My formula is >> =VLOOKUP(A2,Customers!$A$2:$C$415,3,FALSE) >> What do I need to do to make things work? > > As a first guess, try adding a zero to the lookup value, viz use instead: > =VLOOKUP(A2+0,Customers!$A$2:$C$415,3,FALSE) > > Adding a zero is just one way of coercing the text numbers in col A to a > real numbers to enable correct matching with the lookup col A in Customers > -- > Max > Singapore > http://savefile.com/projects/236895 > xdemechanik > --- > "Neophyte" wrote: >> I have exported from an Order Entry program to comma delimited text file >> customer purchases. The file has more entries than Excel can handle on >> one >> worksheet. So, I imported the file data into Access (107,000+entries). I >> then used Excel's "Query Database" feature to import customer level >> purchases into 4 worksheets. The Order Entry program didn't export >> customer >> names, only customer numbers. So, I exported the customer info to another >> text file and imported it into the Excel Workbook, only 451 entries . The >> problem is when I did a vlookup function to retrieve the customer name >> into >> the worksheet with the customer number, it works when I go into a >> customer >> number cell with "F2" and exit. Otherwise, I get the #N/A error because I >> have it set to false. My formula is >> =VLOOKUP(A2,Customers!$A$2:$C$415,3,FALSE) in the invoice worksheets. I >> am >> using Office 2000. >> What do I need to do to make things work? I know this hard to follow and >> trying to explain it is not easy but any help is greatly appreciated. I >> know >> more about Excel than Access and that isn't saying much! >> Thank you very much. >> Lee

Re: Vlookup problem with Access Query import into Excel 2000 Max 17.07.2006 02:31:01 Lee, you're welcome ! Glad it worked for you. Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Neophyte" wrote: [Quoted Text] > That did the trick! I forgot to mention about trying Trim, then Clean, then > Cell Format to number and none of them working. I wish that I could be > updated with new info as easily as the programs but then I wouldn't have the > chance to say thank you very much. > Lee