Reducing a cell value using vlookup

[TWW20]

Hello,

I have come to a sticking point and hoping someone can help.

I have two arrays of numbers. The first is the product barcode and current stock, lets call this the "stock array". The second is the same product barcode and the number ordered by customers, the "order array".
What I want to do is use a conditional VLOOKUP so that if the barcode appears in the "order array" I want the stock value in the "stock array" to decrease by the corresponding value in the "order array".

I have done a VLOOKUP on the barcode in the "order array" to return the number in stock in the "stock array" and created a new cell with the revised in stock value ("stock array" stock number - "order array" qty ordered)

Now I need some kind of VLOOKUP to use only the barcodes in the "order array" to return the new stock value and, very importantly, not to change any of the "stock array" stock values that aren't on the

order array.

Also, the spreadsheet is really slow, is there a way to define the table range so that when I do a search, it only searches that area? There are 7k rows and 10 columns in the main sheet, and 4 further sheets.

Sorry this is hard to explain by text but any help would be appreciated!

Thanks

[#2]

@TWW20 

Trust me. We want to preserve our beginning balances. From those we want to subtract issues to obtain our ending balances. We do not want to overwrite beginning balances because that will destroy any chance of fixing errors. Besides, Excel's formulas cannot overwrite literals (hard coded values). VBA can, but not formulas.

I also urge everyone to use tables because they are dynamic named ranges that will automatically extend formulas as we add orders or stock items and make formulas self documenting. Below is a picture of how I would do it and attached is the spreadsheet with tables and formulas.

BTW - I'm using SUMIFS instead of VLOOKUP because it is very likely that we could have multiple orders for the same SKU. VLOOKUP will only find one entry. Not all.

ulas.