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

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

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

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

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

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
---

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

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