Find a date placed in random order from cumulative value adjacent

[sipa]

Didn't quite know how to word the title, hope this explanation helps

Column A has dates in a random order, column B has Values.

I would like to find the date adjacent to a sum value, that sum value could reside in C1. Put the date found in C2.

many thanks

[Sam Capricci]

it sounds like an index/match might work but when you writ sum value vs values in col B, I'm not sure.
But this is how I'd do it if index/match would satisfy your needs.
=index(A:A,match(c1,B:B,0)) and put that in C2. But again, this would work if you are trying to match a value in C1 that is found somewhere in column B and return the adjacent date value in col A. If however a sum is what you have in C1 then I'm not sure how the formula might have to change.

[Tony Valko]

Can you post a SMALL sample file and show us what result you expect?

A SMALL file will have about 20 rows worth of data.

[sipa]

Thanks for the reply's.


As requested please see the attached sample file, notes on the sheet.

thanks again

[newdoverman]

Insert a helper column (column C) and enter this formula in C2 and fill down. The sum to be found is now in D2 because of the column insertion:

Please Login or Register  to view this content.

This will sum the values in column B until the target is reached.

To find the Data associated with the total enter this formula in E2 and format as a date:

Please Login or Register  to view this content.

	A	B	C	D	E
1	Date	Value	sum	date at	Date
2	25 April	66	66	759	23/05/2015
3	03 May	66	132
4	11 May	66	198
5	05 May	66	264
6	15 May	66	330
7	23 May	66	396
8	31 May	33	429
9	25 May	33	462
10	04 June	33	495
11	12 June	33	528
12	14 June	33	561
13	05 May	66	627
14	15 May	66	693
15	23 May	66	759
16	31 May	66
17	25 May	66
18	05 May	66
19	15 May	33
20	23 May	33
21	31 May	33
22	25 May	33
23	04 June	33
24	12 June

[sipa]

Newdoverman

Thanks for your help, your solution does indeed work.
I was hoping to avoid the use of helper columns, is there anyway other way to get the solution without the need for the extra column ?

[Tony Valko]

Try this array formula**:

=INDEX(A2:A24,MATCH(TRUE,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B24)-ROW(B2)+1))>=C2,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

[sipa]

Tony

Thank you very much, perfect solution.

Ron, thanks for taking the time to post your solution too.

regards

[Tony Valko]

You're welcome. Thanks for the feedback!