Find next bigger value in two-way lookup

[Skovgaard]

Hello Experts,

Hope you can help.
Below table shows a running balance on a stock in quantities.
My goal is to find, when a certain order can be delivered.

Using two Xlookup-functions, I'm able to find the result, if there's enough pcs on stock.
But if there isn't enough items on stock, then I need to find when there will be.

Example:
- Requested delivery date from customer: 2021-33
- Item1
- 5 pcs

Correct result should be "2021-35", hence complete delivery can be fulfilled.

Hope you understand the challenge, otherwise don't hesitate to write your questions.

Running stock balance:

Item no.: / YYYY-Week	2021-31	2021-32	2021-33	2021-35
Item 1	10	0	3	25
Item 2	5	10	15	12
Item 3	0	0	10	15

/Skovgaard

 

[Skovgaard]

Replaced FormulaR1C1 with Formula2R1C1 and it worked, thanks for your help!

/Ulrik

 

[Peter_SSs]

You're welcome. Glad you got it sorted.

 

[Skovgaard]

You're welcome. Glad you got it sorted.

Re. the negative balance protection, I'm thinking that below test will do the trick. If it's true, then it is supposed to add one extra week.
I've tried, without luck, to make this test in VBA. I've tried different things, see below.

Anyone who can help with that?

 If Application.WorksheetFunction.Min(Application.WorksheetFunction.Offset(PivotWeek, Application.WorksheetFunction.Match(item, PivotItem, 0) - 1, 0)) < 0 Then

'or
                                 
         Set rItem = Range("PivotWeek").Offset(Application.WorksheetFunction.Match(item, "PivotItem", 0) - 1, 0)

/Skovgaard