Vlookup function

[Emma]

I am working on a worksheet that has a vlookup function,
which works, but I need to have it leave a cell blank if
the lookup value cell is blank.

Col A Col B Col C Col D
Item Price Quantity Total
drop down this should User input Price*quantity
menus in be updated validated to
this column. using a allow only
vlookup. greater than
zero.

If no item is selected from the drop down menu in Col A,
Col B returns the error #N/A. Does this only work if Col
A has information in the cell? The Table Array has 10
items in col J and price in Col K.

Would appreciate any help you could give. I have been
unable to locate any information for this type of problem.

Thanks,
Emma

 

[Laura Cook]

=IF(A2="","",VLOOKUP(A2,table,x,0))

 

[Kevin Stecyk]

Emma,

The word wrap has made things confusing. But from your question I gather,
you want a blank instead of #NA.

Here is how you can do that.

Your prior formula might have looked like

=vlookup(LookUpValue, TableArray, Index, RangeLookup)

And often it returned a #NA when the value could not be found.

Change the equation to....

=if(iserror(vlookup(LookUpValue, TableArray, Index, RangeLookup), "",
vlookup(LookUpValue, TableArray, Index, RangeLookup))

It says, if there is an error, display "" which is a blank.

Otherwise if there is no error, evaluate the lookup as per usual.

Regards,
Kevin

 

[J.E. McGimpsey]

Note that this will mask errors other than the one the OP asked for
(i.e., no lookup value). That may be OK, or it may prevent the user
from seeing legitimate NA's from failure to match, errors in the
lookup table, etc.

In general it's better to limit the errors that one masks, for
instance:

=IF(ISNA(MATCH(LookUpValue, OFFSET(TableArray,,,,1),
RangeLookup)), "", VLOOKUP(LookUpValue, TableArray, Index,
RangeLookup))

 

[Kevin Stecyk]

J.E. McGimpsey,

Your points are well taken. I tend to use iserror indiscriminately.
Perhaps now is a good time to change.

Regards,
kevin

 

[Emma]

I tried your formula and received an error message. It
keeps pointing to the "" part of the formula.

=IF(ISERROR(VLOOKUP(A2,G2:H11,2),"",VLOOKUP
(A2,G2:H11,2,0))

A2 = ITEM
G2:H11 = TableArray (G col is item, H col is price)
B2 = PRICE
C2 = QUANTITY
D2 = B2*C2

I now find that the formula in Col D returns #VALUE! if
Cols B & C are blank. Can you help.

Thanks!
Emma

 

[Emma]

Correction on formula.

=IF(ISERROR(VLOOKUP(A2,$G$2:$H$11,2),"",VLOOKUP
(A2,$G$2:$H$11,2,0))

 

[Emma]

Please disregard request for solution to error #VALUE! in
Col D. I figured out the problem.

=IF(B2="","",B2*C2)

If B2 is blank, then D2 is blank. If not, it gives the
total of B2*C2.

Thanks for your help.

Emma

 

[Kevin Stecyk]

Emma,

Glad you are on your way!

Regards,
Kevin