Find the last element in a sum of elements that is equal to or greater than the input (formula, no V-CodePudding

I think this is a tricky one.

I'm trying to make a bucket system where you put in a number and you sum up cells in a line until they are equal to or larger than your number. Then you select that cell.

EG below.

Input is 3. 
A = 0
A B = 1
A B C = 3 
Answer is C

Input is 4
A B C = 3 
A B C D = 6
Answer is D because it's the next bracket up.

Pretty easy algorithm to do in a macro but I'm trying to avoid VBA.

CodePudding user response：

It would be easier if you use a helper column. As per my below screenshot I have used =SUM($B$3:$B3) to C3 cell and drag down till need. Then use below formula to get desired result.

=@FILTER(A3:C10,C3:C10>=B1)

CodePudding user response：

=XLOOKUP(B1,SCAN(0,B3:B10,LAMBDA(κ,λ,κ λ)),A3:A10,,1)