If function

[if function returning a #n/a]

I know this has got to be simple but I'm not seeing it. I paste an order received on a seperate worksheet, and I want the sheet to paste the amount ordered into this but when I get to an item i did not recieve it returns a #n/a. I can not use the column or the product of a number in the column if one of the cells is #n/a. If A123 is not on MBM2 sheetI want a value of "" or 0 returned ? Thanks in advance for your hel

=IF(VLOOKUP(A123,'MBM 2'!$B$4:'MBM 2'!$K$208,6,'MBM 2'!$B$4:'MBM 2'!$K$208)=0,"",VLOOKUP(A123,'MBM 2'!$B$4:'MBM 2'!$K$208,6,'MBM 2'!$B$4:'MBM 2'!$K$208)

 

[Jane]

Try =IF(ISERROR(VLOOKUP(A123,'MBM 2'!$B$4:'MBM 2'!
$K$208,6,'MBM 2'!$B$4:'MBM 2'!$K$208)),"",VLOOKUP
(A123,'MBM 2'!$B$4:'MBM 2'!$K$208,6,'MBM 2'!$B$4:'MBM 2'!
$K$208))

Jane

-----Original Message-----
I know this has got to be simple but I'm not seeing it.

I paste an order received on a seperate worksheet, and I
want the sheet to paste the amount ordered into this but
when I get to an item i did not recieve it returns a #n/a.
I can not use the column or the product of a number in the
column if one of the cells is #n/a. If A123 is not on
MBM2 sheetI want a value of "" or 0 returned ? Thanks in
advance for your help

=IF(VLOOKUP(A123,'MBM 2'!$B$4:'MBM 2'!$K$208,6,'MBM 2'!

$B$4:'MBM 2'!$K$208)=0,"",VLOOKUP(A123,'MBM 2'!$B$4:'MBM
2'!$K$208,6,'MBM 2'!$B$4:'MBM 2'!$K$208))

 

[drabbacs]

You're close.

=if(iserror(vlookup_phrase),"",vlookup_phrase))

also see help for isna, iserr, isblank for other tips.

Drabbacs

-----Original Message-----
I know this has got to be simple but I'm not seeing it.

I paste an order received on a seperate worksheet, and I
want the sheet to paste the amount ordered into this but
when I get to an item i did not recieve it returns a #n/a.
I can not use the column or the product of a number in the
column if one of the cells is #n/a. If A123 is not on
MBM2 sheetI want a value of "" or 0 returned ? Thanks in
advance for your help

=IF(VLOOKUP(A123,'MBM 2'!$B$4:'MBM 2'!$K$208,6,'MBM 2'!

$B$4:'MBM 2'!$K$208)=0,"",VLOOKUP(A123,'MBM 2'!$B$4:'MBM
2'!$K$208,6,'MBM 2'!$B$4:'MBM 2'!$K$208))

 

[Rick M]

Thanks that worked