Return value once sum is greater than a certain number

[simmo86]

I have a row of numbers from 1-20 in row A
Row B contains random numbers i.e. 0.1, 0.3, 0.2, 0.4

I need a formula that can effectively go along row b adding up the values until it is above the value "1" and then return the value in row A.

I have a feeling it has to be some kind of h lookup with a sumif?

Any help would be much appreciated.

Thanks in advance

[daddylonglegs]

A and B are columns, nor rows....

Assuming data in columns try this "array formula"

=INDEX(A1:A20,MATCH(1,SUBTOTAL(9,OFFSET(B1,,,ROW(B1:B20)-ROW(B1)+1)))+1)

confirmed with CTRL+SHIFT+ENTER

[simmo86]

Sorry, had a senior moment! The data is in rows not columns, can i repeat that formula with "column" rather than "row"?

[simmo86]

And ideally at the end of the row it just returns the lookup value. I have attached a demo

[daddylonglegs]

SUBTOTAL function only works for columns so a slightly different approach.....

Try this formula in N2

=INDEX(A$1:L$1,MATCH(TRUE,SUMIF(OFFSET(A2,,,,COLUMN(A2:L2)-COLUMN(A2)+1),"<9.99E+307")>=1,0))

confirmed with CTRL+SHIFT+ENTER

If the values in row 1 will always start at 1 (and increase by 1 each time) then you can do without the INDEX part, i.e. just

=MATCH(TRUE,SUMIF(OFFSET(A2,,,,COLUMN(A2:L2)-COLUMN(A2)+1),"<9.99E+307")>=1,0)

Note: as F3 is 0.5 not 0.05 N3 value will be 6 not 7......

[simmo86]

That is absolutely brilliant!! The actual data in row 1 is not increasing by 1 each time. So the first one works perfectly!

Thank you so much!! Such a life saver!!

[simmo86]

So sorry, one last thing to complete this..... how would you write the formula in reverse? i.e. I was to put "5" into a cell and then it would return the sum of everything in that row up to that point?