CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Leighholling
Leighholling

Reputation: 58

Excel Formula to loop through a range

I am looking for an excel formula not VBA I can do it with VBA but there is a reason I do not want to use VBA.

For example I have 1 column and 6 rows as per below. I want to specify a number in an index to bring back the value.

Row

  1. Apple
  2. Orange
  3. Banana
  4. Strawberry
  5. Grape
  6. Kiwi

I would like the formula to start at 1 and then count how ever many rows I ask it to and go back to 1 after it gets to 6

So for example if I did =INDEX(A1:A6,9) then the result would be Banana OR if I did =INDEX(A1:A6, 18) the result would be Kiwi.

I have tried all sorts to get it to work using index but it is just not working, every time I use a number outside the range it cannot be resolve.

Upvotes: 0

Views: 1918

Answers (1)

BigBen
BigBen

Reputation: 49998

Using MOD:

=INDEX(A1:A6,IF(MOD(9,6)=0,6,MOD(9,6)))

enter image description here

Other variations:

=INDEX(A1:A6,IF(MOD(9,6),MOD(9,6),6))

=INDEX(A1:A6,MOD(9-1,6)+1)

=INDEX(A1:A6,MOD(18,6)+6*NOT(MOD(18,6)))

Upvotes: 1

Related Questions