 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008VLOOKUP Formula
Hi, I have a workbook, one sheet for purchase orders, which collects data from the Stocklist sheet (excel 2003). I want to insert a vlookup formula into cell B10 of the PO, dependant upon a code i type into cell A10 of the PO, which brings across the corresponding description, then the same formula, ammended, to bring across the corresponding size into D10, price into E10. I tried this but it had too many arguments: =IF($A$10="","",VLOOKUP(StockList!$A10,$A10:$H10015,COLUMNS($A$3:B10),2,0))  SignatureRegards, Sarah Just remove the 2 at the end: =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$10015,COLUMNS($A10:B10),0)) SignatureBiff Microsoft Excel MVP > Hi, > [quoted text clipped - 6 lines] > tried this but it had too many arguments: > =IF($A$10="","",VLOOKUP(StockList!$A10,$A10:$H10015,COLUMNS($A$3:B10),2,0)) Thanks so much for your reply Biff, however, the formula still doesn't work - am now getting the #N/A error, any ideas on what i'm doing wrong??? SignatureRegards, Sarah > Just remove the 2 at the end: > [quoted text clipped - 10 lines] > > tried this but it had too many arguments: > > =IF($A$10="","",VLOOKUP(StockList!$A10,$A10:$H10015,COLUMNS($A$3:B10),2,0)) #N/A means the lookup_value can't be found in the lookup_table. =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$10015,COLUMNS($A10:B10),0)) Are you sure the references are correct? Shouldn't these be the same reference: =IF($A10.....VLOOKUP(StockList!$A10 According to your formula you're testing A10 to see if it is blank but A10 is also the top left cell of your lookup_table. I'm guessing that your formula should be: =IF($A10="","",VLOOKUP($A10,StockList!$A$10:$H$10015,COLUMNS($A10:B10),0)) SignatureBiff Microsoft Excel MVP > Thanks so much for your reply Biff, however, the formula still doesn't > work - [quoted text clipped - 19 lines] >> > tried this but it had too many arguments: >> > =IF($A$10="","",VLOOKUP(StockList!$A10,$A10:$H10015,COLUMNS($A$3:B10),2,0)) Thank you Biff! That works perfectly. SignatureRegards, Sarah > #N/A means the lookup_value can't be found in the lookup_table. > [quoted text clipped - 35 lines] > >> > tried this but it had too many arguments: > >> > =IF($A$10="","",VLOOKUP(StockList!$A10,$A10:$H10015,COLUMNS($A$3:B10),2,0)) You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > Thank you Biff! That works perfectly. > [quoted text clipped - 42 lines] >> >> > tried this but it had too many arguments: >> >> > =IF($A$10="","",VLOOKUP(StockList!$A10,$A10:$H10015,COLUMNS($A$3:B10),2,0)) That is a block array formula which seems unnecessary, just try =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$10015,COLUMNS(B1),FALSE)) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thanks so much for your reply Biff, however, the formula still doesn't > work - [quoted text clipped - 19 lines] >> > tried this but it had too many arguments: >> > =IF($A$10="","",VLOOKUP(StockList!$A10,$A10:$H10015,COLUMNS($A$3:B10),2,0)) The #N/A could also be reflective of data inconsistency between the lookup value and the vlookup's col A Assuming the vlookup's col A contains text numbers while the lookup value "StockList!$A10" contains real numbers you could try changing the lookup value "StockList!$A10" in: >> ... VLOOKUP(StockList!$A10,$A$10:$H$10015 using these alternatives: a. ... VLOOKUP(StockList!$A10&"",$A$10:$H$10015 where the &"" part will convert the real number to a text number b. ... VLOOKUP(TEXT(StockList!$A10,"0000"),$A$10:$H$10015 where the TEXT function will convert it to text number & pad leading zeros as may be required. Amend the "0000" bit to suit the data structure in the vlookup's col A. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.