Can't get array formula to work in MATCH function

[Chas Grad]

I want to find the last location in a series of numbers where the cumulative
sum of the numbers does not exceed a target value.
This is easy to do using the MATCH function and a second list that contains
the cumulative sum of the values in the first list.
In the example below, A1:A1 contains the list of interest and B1:B10
contains the cumulative sums.
The formula MATCH(D2,B1:B101) in D3 finds the location of the first value in
B1:B10 that is less than or equal to the target.
I want to achieve that result without using the second list containg the
cumulative sums.
I created an array formula to yield the results of col B for the MATCH
function to work on, but the result is #NA
The formula I used was {MATCH( D2, SUM(OFFSET(A1,0,0,A1:A10,1), 1)}
When I just put the offset formula in a cell and hit F9 I see this result:
{1;1;1;1;1;1;1;1;1;1}
Any suggestions on how I can accomplish my goal of not having to have the
formulas in col B?
I was unsuccessful at pasting a picture here. Here's a crude reproduction
of what the sample sheet looked like:
A B C D
1 1 1 Target 35
2 2 3 Location 7 ( =MATCH(D2, B1:B10,1) )
3 3 6
4 4 10
5 5 15
6 6 21
7 7 28
8 8 36
9 9 45
10 10 55

 

[T. Valko]

Try this array formula** :

=MATCH(D2,SUBTOTAL(9,OFFSET(A1:A10,,,ROW(A1:A10)-MIN(ROW(A1:A10)))))-(SUM(A1:A10)>D2)

 

[Chas Grad]

THANKS!
It works. I'll need to spend some time with it to figure out why, but I
really appreciate geting an answer that does the job - especially so quickly.
Thanks again.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Chas Grad]

I made the problem harder by not including in the sample the fact that I
already had the list {1, 2, 3…) available in the real spreadsheet and didn’t
need to resort to the ROW function for that. Judging by how quickly you
responded, it didn’t faze you, though. The key concept I missed (even after
staring at your elegant solution for a while) was that I needed the height of
the array returned by offset to be other than 1. A dumb oversight now that I
see it. I still don’t understand why it works with SUBTOTAL and not with
SUM, though. Is there a concept I am missing or is it an Excel quirk?
The formula in the actual spreadsheet is (now) as follows. I’m trying to
figure in what year limited partners in an oil well get their original
investment back.
MATCH(Initial_Investment, SUBTOTAL(9,OFFSET(net_cash_flow,,,Production_Years
) ) ) - 1

Thanks again for your help. I really wanted to avoid another row of numbers
in this spreadsheet.

 

[T. Valko]

SUBTOTAL returns an array of sums based on the height argument. SUM would
return a *single* sum value of the entire range. So we need to use SUBTOTAL
to "step" through the range.