| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP 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:$H1001 5,COLUMNS($A$3:B10),2,0)) -- Regards, Sarah |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
VLOOKUP Formula
Just remove the 2 at the end:
=IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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:$H1001 5,COLUMNS($A$3:B10),2,0)) -- Regards, Sarah |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
VLOOKUP Formula
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??? -- Regards, Sarah "T. Valko" wrote: Just remove the 2 at the end: =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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:$H1001 5,COLUMNS($A$3:B10),2,0)) -- Regards, Sarah |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
VLOOKUP Formula
#N/A means the lookup_value can't be found in the lookup_table.
=IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,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$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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??? -- Regards, Sarah "T. Valko" wrote: Just remove the 2 at the end: =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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:$H1001 5,COLUMNS($A$3:B10),2,0)) -- Regards, Sarah |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
VLOOKUP Formula
That is a block array formula which seems unnecessary, just try
=IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,COLUMNS(B1),FALSE)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sarah" wrote in message ... 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??? -- Regards, Sarah "T. Valko" wrote: Just remove the 2 at the end: =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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:$H1001 5,COLUMNS($A$3:B10),2,0)) -- Regards, Sarah |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
VLOOKUP Formula
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
VLOOKUP Formula
Thank you Biff! That works perfectly.
-- Regards, Sarah "T. Valko" wrote: #N/A means the lookup_value can't be found in the lookup_table. =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,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$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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??? -- Regards, Sarah "T. Valko" wrote: Just remove the 2 at the end: =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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:$H1001 5,COLUMNS($A$3:B10),2,0)) -- Regards, Sarah |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
VLOOKUP Formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Sarah" wrote in message ... Thank you Biff! That works perfectly. -- Regards, Sarah "T. Valko" wrote: #N/A means the lookup_value can't be found in the lookup_table. =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,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$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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??? -- Regards, Sarah "T. Valko" wrote: Just remove the 2 at the end: =IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$100 15,COLUMNS($A10:B10),0)) -- Biff Microsoft Excel MVP "Sarah" wrote in message ... 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:$H1001 5,COLUMNS($A$3:B10),2,0)) -- Regards, Sarah |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
| Using a Vlookup within an IF formula | Excel Discussion (Misc queries) | |||
| Vlookup using a formula possible? | Excel Worksheet Functions | |||
| vlookup formula | Excel Discussion (Misc queries) | |||
| Vlookup formula | Excel Worksheet Functions | |||
Linear Mode
