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

#### pewzie

##### New Member
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### RasGhul

##### Well-known Member
Hi pewzie,

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

Can you post some sample data so we can help?

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.

1,167,097
Messages
5,852,154
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.

### Which adblocker are you using?

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

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