Vlookup specific item code between set date range ( week commencing+6 ) and sum and results

pewzie

New Member
Joined
Jan 12, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am looking to pull some data out of our system into a spreadsheet to calculate any shortfalls in (stock at hand)-(weekly sales)+(incoming stock that week).

I have a master results sheet. Another sheet which has stock at hand and weekly sales on and then a third sheet which shows amount of stock coming in and and on which date.

I can vlookup the item ( a product code ) to show stock at hand and then minus weekly sales and show that result for each w/c date through the calendar. The bit I am struggling on is looking up the incoming stocks and adding that to the calculation.

So essentially on the master sheet I need to vlookup on another sheet the product code. Then pull any incoming amounts of stock for that code if it falls between the available w/c date which is at the top of my master spreadsheet.

So at the moment I can find the first instance of that code on the sheet with;

=VLOOKUP($A$14,PD!$A$1:$K$247,8,FALSE) and it brings through the first incoming amount. I need then to check if its between the date range for that columns w/c ie 01/01/22+6 days. Anything after that date the check would be in the next w/c column on my master sheet. The other thing I need to do is sum the amounts if there were more than one date, so if 500 was arriving on 01/01/22 and then another 500 on the 05/01/22 I would need the answer to be 1000 for that w/c date of 01/01/22+6days.

I can then mix that with the other parts of the lookups so the ultimate answer would be

Localstock-avgsales+incomingstock. I am looking for this to basically show me any shortfalls at any point. At the moment the system will say we have for example 14 weeks worth of stock on order, but doesnt tell me without a bit of work if its all going to arrive in time to avoid any out of stock days. It could be 14 weeks worth of stock arriving all on one date 4 weeks after we would have been out of stock.

Any help greatly appreciated. I have done many vlookups but never between certain conditions and not ever having to sum multiple data.

Many thanks and apologies if the above is not clear enough to get a response any questions I will answer of course.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
Hi pewzie,

If your calculating amounts between dates, this might be easier with sumifs.

Can you post some sample data so we can help?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,096
Messages
5,852,153
Members
431,488
Latest member
ePayslip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top