Rotate an array index?

[gifariz]

Suppose I have an array X = {"A";"B";"C"}
I want to rotate the index by an integer i, suppose i = 1, so that Y = Rotate(X,i) = {"C";"A";"B"}
Is there excel function to do this?
What I do now is Y = LET(X,H18#,i,H16,n,COUNTA(X),ix,SEQUENCE( n)-H16,INDEX(X,IF(ix<1,ix+n,ix)))
which is quite mothful. Thanks

 

[Fuji]

You mean like this?

Y = Application.Index(X, WorksheetFunction.Sequence(UBound(X), , UBound(X), -1), 0)

 

[Cubist]

Try:

Book1
	A	B	C	D
1	Array	i		Result
2	A	2		B
3	B			C
4	C			A
Sheet3

Cell Formulas
Range	Formula
A2:A4	A2	= {"A";"B";"C"}
D2:D4	D2	=LET(r,ROWS(A2#),INDEX(A2#,MOD(SEQUENCE(r)-1-B2,r)+1))
Dynamic array formulas.

 

[gifariz]

Edit: Y = LAMBDA(X,i,LET(n,COUNTA(X),seq,SEQUENCE( n),ix,seq-i,INDEX(X,IFS(i=0,seq,i>0,IF(ix<1,ix+n,ix),i<0,IF(ix>n,ix-n,ix)))))

 

[gifariz]

Try:

Book1
	A	B	C	D
1	Array	i		Result
2	A	2		B
3	B			C
4	C			A
Sheet3

Cell Formulas
Range	Formula
A2:A4	A2	= {"A";"B";"C"}
D2:D4	D2	=LET(r,ROWS(A2#),INDEX(A2#,MOD(SEQUENCE(r)-1-B2,r)+1))
Dynamic array formulas.

This looks close. But when I try A2={"A";"B";"C";"D"}, and B2=-1, it returns incorrect result. Maybe it has something to do with MOD divisor?

Edit: oh great, you updated your answer. It works great now. Thank you!

 

[Cubist]

you updated your answer. Thank you!

You're welcome. I had a hardcoded value in the formula. It needed to be the number of rows.