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
  • 3,192
  • 2
  • 5
  • 19

1 Answers1

2

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
  • 53,146
  • 6
  • 36
  • 60