Point at which a range of cells equals specified value

[allemanable]

I have data that shows the amount of earnings earned each quarter by customer on sheet 1
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Quarter[/TD]
[TD]Quarterly Earnings[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]1Q16[/TD]
[TD]$5.00[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]2Q16[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]3Q16[/TD]
[TD]$15.00[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]1Q16[/TD]
[TD]$5.00[/TD]
[/TR]
</tbody>[/TABLE]







On sheet 2, data shows
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Earnings Due[/TD]
[TD]Quarters To Be Paid[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]$25.00[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]$5.00[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]
I am trying to find a formula/macro that will look up customer 1234, find the point at which earnings = $25.00 and bring back the quarter at which point this happens (2Q16). Any suggestions??

 

[jarjarbingie]

I have data that shows the amount of earnings earned each quarter by customer on sheet 1
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Quarter[/TD]
[TD]Quarterly Earnings[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]1Q16[/TD]
[TD]$5.00[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]2Q16[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]3Q16[/TD]
[TD]$15.00[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]1Q16[/TD]
[TD]$5.00[/TD]
[/TR]
</tbody>[/TABLE]







On sheet 2, data shows
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Earnings Due[/TD]
[TD]Quarters To Be Paid[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]$25.00[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]$5.00[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]
I am trying to find a formula/macro that will look up customer 1234, find the point at which earnings = $25.00 and bring back the quarter at which point this happens (2Q16). Any suggestions??

Sheet1:

D2=SUMIF($A$2:A2,A2,$C$2:C2) drag down


Sheet2:

C2=INDEX(Sheet1!$B$2:$B$5,MATCH($A2,Sheet1!$A$2:$A$5,0)+MATCH($B2,OFFSET(Sheet1!$D$1,MATCH($A2,Sheet1!$A$2:$A$5,0),0,COUNTIF(Sheet1!$A$2:$A$5,$A2),1),1)-1)