vlookup product & certain quantity in a cumulative pivot table and return the date (header of that pivot table

[KristofGoossens]

Hi everyone,

(using just Excel 365)

I have two data lists that simply put consist of
backlog data (sorted by SKU - committed ship date - date of creation) amongst other data + a cumulative sum of the backlog for comparison to the supply (which is the second list)
pivot table (for aggregation on SKU) with SKU in row, the dates (or weeks to simplify) of arrival in the columns

See below for hypothetical unit with 900 units in backlog and a couple of hundred incoming. What would be the formula to vlookup the cumulative quantity of the backlog to the first value of the pivot table that 's higher and return the date attached to that value?

for SO000008 for example, I know I need at least 625 incoming units to make sure I can deliver that order (and check whether the original committed ship date is still valid), so how would I "vlookup" 625 into the pivot table, get to the first value (of course on the row of SKU XYZ because there are lots of other SKU's) that is bigger than 625 and return the ETA-date?

Thanks in advance for your help!

 

[awoohaw]

Hi, Will this work:

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G	H	I
1	Ordernumber	SKU	Committed Ship Date	Created Date	QTY	Cumulative Qty	order fulfillment date
2	SO000001	XYZ	2/10/2022	1/12/2022	20	20	2/8/2022
3	SO000002	XYZ	2/17/2022	1/12/2022	50	70	2/8/2022
4	SO000003	XYZ	2/24/2022	1/12/2022	100	170	2/15/2022
5	SO000004	XYZ	3/3/2022	1/12/2022	250	195	2/15/2022
6	SO000005	XYZ	3/10/2022	1/12/2022	30	225	2/22/2022
7	SO000006	XYZ	3/17/2021	1/12/2022	150	375	3/1/2022
8	SO000007	XYZ	3/24/2020	1/12/2022	100	475	3/8/2022
9	SO000008	XYZ	4/1/2019	1/12/2022	150	625	3/22/2022
10	SO000009	XYZ	4/8/2018	1/12/2022	200	825
11	SO000010	XYZ	4/15/2017	1/12/2022	75	900
12
13
14
15	Cumulative Pivot Data
16	SKU	2/8/2022	2/15/2022	2/22/2022	3/1/2022	3/8/2022	3/15/2022	3/22/2022	3/29/2022
17	XYZ	100	200	300	400	500	600	700	800
18
BacklogFullfillment

Cell Formulas
Range	Formula
G2:G9	G2	=INDEX($A$16:$I$16,XMATCH($F2,FILTER($A$16:$I$17,$A$16:$A$17=$B2),1,1))

 

[KristofGoossens]

Hi, yes, this looks very helpful at first glance! Thanks a lot!

I will test this formula on my actual data today and tomorrow. Thanks again!!

 

[awoohaw]

Hi, yes, this looks very helpful at first glance! Thanks a lot!

I will test this formula on my actual data today and tomorrow. Thanks again!!

My pleasure have a great day!