Is VLOOKUP what I need?

[Gary Ridgway]

I have one spreadsheet with all the stock my supplier has available with
retail and trade prices. Columns are: part number, description, retail,
trade.

I have just completed another sheet which I want to keep separate with
what stock I have in my workshop.

I wish to add another column to the supplier sheet which looks at my
stock sheet and puts the stock level in.

As I only have a fraction of what the supplier has on offer, there are a
lot of parts I don't have a stock level for. If I use VLOOKUP I can get
the stock level for the parts I have, but for all the parts I don't have
I get #NA. VLOOKUP is looking for an exact match which isn't going to
happen unless I have all the stock my supplier has.

I have tried the IF statement to try to make any #NA cell read zero, but
to no avail.

Anyone got an idea on how to make the #NA cells read zero if I haven't
that particular stock item on my sheet?

 

[Joerg Mochikun]

You could of course use an IF statement like
=IF(ISERROR(vlookupformula),0,vlookupformula)
but I wonder if the #N/A is not more to the point: It tells you that you
don't have the good in your list of stock items, probably because you never
ordered it, while 0 would mean, that the item is in your list, but the
current stock level is zero. For me these are 2 different statuses and you
would loose this important distinction if you set the values for both cases
to zero.

Joerg

 

[Gary Ridgway]

You do have a point there Joerg.

You could of course use an IF statement like
=IF(ISERROR(vlookupformula),0,vlookupformula)
but I wonder if the #N/A is not more to the point: It tells you that you
don't have the good in your list of stock items, probably because you never
ordered it, while 0 would mean, that the item is in your list, but the
current stock level is zero. For me these are 2 different statuses and you
would loose this important distinction if you set the values for both cases
to zero.

Joerg

 

[Gord Dibben]

ISERROR will hide all errors, not just the #N/A

Use ISNA function.

=IF(ISNA(vlookup formula)),"no data",vlookup formula))

=IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"no
data",VLOOKUP(G1,$A$1:$F$31,2,FALSE))


Gord Dibben MS Excel MVP