If function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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.
Click to expand...
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'!
Click to expand...
$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))
 
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.
Click to expand...
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'!
Click to expand...
$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))
 
Back
Top