VLOOKUP Formula

S

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

T. Valko

Just remove the 2 at the end:

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

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


T. Valko said:
Just remove the 2 at the end:

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

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


Sarah said:
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???
 
B

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)



Sarah said:
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???
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top