VLOOKUP Formula

[Sarah]

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

 

[T. Valko]

Just remove the 2 at the end:

=IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$10015,COLUMNS($A10:B10),0))

 

[Sarah]

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

Just remove the 2 at the end:

=IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$10015,COLUMNS($A10:B10),0))

 

[T. Valko]

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


--
Biff
Microsoft Excel MVP

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

 

[Bob Phillips]

That is a block array formula which seems unnecessary, just try

=IF($A10="","",VLOOKUP(StockList!$A10,$A$10:$H$10015,COLUMNS(B1),FALSE))


--
---
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 -
am now getting the #N/A error, any ideas on what i'm doing wrong???

 

[Max]

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

---

 

[Sarah]

Thank you Biff! That works perfectly.

 

[T. Valko]

You're welcome. Thanks for the feedback!