1

I'm breaking my head on the following:

I have 15 as a lookup value in B1

In A1:A4 for each cell the value is 5. (I real these are all different values).

I want excel to SUM the values of A1:A4 one by one and if the search value is reached by that sum, return the row number. So for this example I want result 3 (=5+5+5) If no exact match then return the closest match smaller than the search value.

I tried something like =MATCH(1,B1=SUM(A1:A4),1) which doesn't work obviously, but I don't know if it's even possible without VBA. Does anybody know a way (without helper column).

P.b
  • 8,293
  • 2
  • 10
  • 25

2 Answers2

3

You may try:

=MATCH(TRUE,--SUBTOTAL(9,OFFSET(A1:A4,,,ROW(A1:A4)))>=B1,0)

I'm using Excel O365, but any other version may need CSE-entering of the above (I'm unsure). Do note OFFSET() is volatile!

The above would return the row where the cumulative sum first >=B1. If your goal as per your question was to get the closest match but smaller than B1 in case there is no exact match:

=MATCH(B1,--SUBTOTAL(9,OFFSET(A1:A4,,,ROW(A1:A4))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
0

And with Office 365 it's worth mentioning it can be accomplished using SCAN:

=XMATCH(B1,SCAN(0,A1:A4,LAMBDA(a,b,a+b)),1)

P.b
  • 8,293
  • 2
  • 10
  • 25