Adding Inventory with items on order

[Soulscream]

I am trying to total numbers from two different cells. Cells A1:B10 are a
part number and quantity. This gives me inventory totals. Right now I have
the B1:B10 cells setup to pull the numbers from 'Sheet2'!B2 - B10, which
works fine, but I have a list of items that I have on order and their
quantities in cells E1:F10. What I want to do is to say if A1 appears in the
array E1:F10 then to add F1 and 'Sheet2'!B1, but when I do my formula I get
back a "#n/a" answer in my cell if the value or part number in my a column
does not appear in my E column. This is my current equation which works if I
have the part number in the E column, but gives "#n/a" if I don't:

=VLOOKUP(A1,$E$1:$F$10,2)+'Sheet2'!B1

If I don't have the number listed on my order list I want the cell to equal
'Sheet2'!B1 and if that equals zero, then I want the cell to be blank.

I'm not sure if I need to do a Search function for A1 and use a boolean or
if the vlookup is the proper function.

Any advice would be much appreciated. Thanks.

 

[Joel]

=if(isna(VLOOKUP(A1,$E$1:$F$10,2)),0,VLOOKUP(A1,$E$1:$F$10,2))+'Sheet2'!B1

 

[Max]

One guess

In Sheet1,
Try in G1
=IF(E1="","",IF(COUNTIF(A:A,E1),SUM(F1,Sheet2!B1),IF(Sheet2!B1="","",Sheet2!B1)))
Copy down