Dynamic Index Reference

[Kaiser958]

I'm having an issue trying to logic out how to create a looping reference for an INDEX or MATCH formula. What I require is for AR3 and down to represent the index location of S2 and down. I need AR to cycle through that index n number of times where n is the value in the Y column associated the index in Y and then increment into the next index. I can not do this with VBA due to organization restrictions and I really want to keep this with formulas and helper cells, no power query. Any advice is appreciated.

 

[Kaiser958]

Yh I copied this formula exactly as you had it and its not working.

=IF(COUNTA($AR$1:$AR1)<SUM($Y$2:$Y$100),IFERROR(IF(COUNTIF($AR$1:$AR1,$AR1)=VLOOKUP($AR1,$S$2:$Y$100,7,0),IFERROR(INDEX($S$2:$S$100,MATCH($AR1,$S$2:$S$100,1)+1,""),AR1),$S2),""))

The VLOOKUP is kicking out a N/A on AR2

 

[Kaiser958]

I got it! I was missing a parenthesis after the +1. Thank you so much! The final formula is:

=IF(COUNTA($AR$1:$AR1)<SUM($Y$2:$Y$100),IFERROR(IF(COUNTIF($AR$1:$AR1,$AR1)=VLOOKUP($AR1,$S$2:$Y$100,7,0),IFERROR(INDEX($S$2:$S$100,MATCH($AR1,$S$2:$S$100,1)+1),""),AR1),$S2),"")

I will likely make small changes to further improve but this is absolutely what I needed!

 

[Legacy 143009]

Glad it did work

 

[Peter_SSs]

Do the column S values always start at 1 and increase by 1 each row?

If so, try this in AR2 (assuming AR1 is empty like your images)

=IF(ROWS(AR$2:AR2)>SUM(Y$2:Y$100),"",AR1+(COUNTIF(AR$1:AR1,AR1)=IFNA(VLOOKUP(AR1,S$2:Y$100,7),0)))

or possibly even this

=IF(ROWS(AR$2:AR2)>SUM(Y$2:Y$100),"",AR1+(COUNTIF(AR$1:AR1,AR1)=N(INDEX(Y$1:Y$100,AR1+1))))

If not, can you tell us more about what might be in column S?

@Flashbond
I would have changed the solution post to yours if you had provided the formula actually in post 9
Please do not make posts like that where the answer you are suggesting is only in a linked file - see #4 (especially the last two paragraphs) of the Forum Rules