Formula that starts where previous row left off

I have a nested if statment that goes down a row until a certain value is found. Once that value is found, it pastes the value in a different sheet. The problem is that if the formula gets to row 71 before it finds a "match", it will paste the proper value, but when it continues, then next row's formula begins at row70, so it will find row 71 and also find a "match", so I will end up with the same line.

Is there a way to start a formula based on the result of the formula in the previous row? For example, if I started searching for a value on row 1 of sheet A and didn't find a match until row 10 of sheet A (result pastes in row 1 of Sheet B), the formula in row 2 of sheet B would begin with evaluating with row 11 of sheet A?


=IF('Allocated Spending Plan - Dec'!I69="Savings",'Allocated Spending Plan - Dec'!B69,IF('Allocated Spending Plan - Dec'!G69="Savings",'Allocated Spending Plan - Dec'!B69,IF('Allocated Spending Plan - Dec'!I70="Savings",'Allocated Spending Plan - Dec'!B70,IF('Allocated Spending Plan - Dec'!G70="Savings",'Allocated Spending Plan - Dec'!B70,IF('Allocated Spending Plan - Dec'!I71="Savings",'Allocated Spending Plan - Dec'!B71,IF('Allocated Spending Plan - Dec'!G71="Savings",'Allocated Spending Plan - Dec'!B71,IF('Allocated Spending Plan - Dec'!I72="Savings",'Allocated Spending Plan - Dec'!B72,IF('Allocated Spending Plan - Dec'!G72="Savings",'Allocated Spending Plan - Dec'!B72,IF('Allocated Spending Plan - Dec'!I73="Savings",'Allocated Spending Plan - Dec'!B73,IF('Allocated Spending Plan - Dec'!G73="Savings",'Allocated Spending Plan - Dec'!B73,IF('Allocated Spending Plan - Dec'!I74="Savings",'Allocated Spending Plan - Dec'!B74,IF('Allocated Spending Plan - Dec'!G74="Savings",'Allocated Spending Plan - Dec'!B74,"")

Okay, here's what I did (your formula is scary )
I created a dummy column to track when Savings appears in G or I. I put it in N. This can be hidden later if you like It looks like this
=IF(OR(I69= "Savings", G69= "Savings"), "S_" & COUNTIF($N$68:N68,"S_*"),"")
This results in a sequence as follows S_0, S_1, S_2 and so on.

Then in the other sheet where you want your results (in my example, it's in Sheet2 starting in A2)
=IFERROR(INDEX('Allocated Spending Plan - Dec'!$B$69:$B$73,MATCH("S_"&ROW(A1)-1,'Allocated Spending Plan - Dec'!$N$69:$N$73,0)),"") (modify ranges as needed.
Questions?

Okay, I fixed your formulas in the dummy columns. For example, in in Y2, you had this

=IF(OR(J2= "Wells Checking", H2= "Wells Checking"), "S_" & COUNTIF($V1:W$68,"S_*"),"")
1. It should be referencing F2 and H2
2. The COUNTIF portion is counting how many matches you've already had for that catagory (thus in that column) and will attach that to "S_"
so should be COUNTIF($Y$1:Y1, "S_*"). As you drag that down, it'll change (i.e. in Y10, it would be COUNTIF($Y$1:Y9,"S_*") )

So the formula in Y2 should be
=IF(OR(F2= "Wells Checking", H2= "Wells Checking"), "S_" & COUNTIF($Y$1:Y1,"S_*"),"")

I am not sure how you are using these values though. You'd need to reference the appropriate column when using Index. For example, for Wells Saving, it would be column AA.
If you could clarify a little how these numbers are going into sheet2, I might be able to help you more.

Okay, gotta go for the day but I set up the formulas in A:D on Sheet2. They are similar to the ones we already discussed. See if you can do the next set. I also wasn't sure where you wanted "Description" taken from. I used & to combine Category and Subcategory in Col Q on the first sheet.

Glad it's all working for you.