Reducing a cell value using vlookup

[CorinneTW]

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.

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

Thanks

[mehmetcik]

1. Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

2. VLookup will not work for you.
You need to be using sumif

Something like:
=SL1 - Sumif(ListOfPartsOrdered, PartOrdered, QtyOrdered)

SL1 = Stock Level for Part No 1.
ListOfPartsOrdered
PartOrdered = Part No 1
QtyOrdered = Qty of Part No 1 ordered on that line


Easier to explain in an actual workbook.

[CorinneTW]

Hello,

Thanks so much for getting back to me.

I have added a sample workbook to the original post. I think I get your SUMIF but if you could show me in the workbook that would be excellent.

Thanks again

Thanks!

[JeteMc]

Attached is a copy of the workbook with the following formula applied:

Please Login or Register  to view this content.

Note that conditional formatting is applied to column C on the (After) Stock sheet to hide zero values.
Let us know if you have any questions.