Excel modulo value for index/match

I’m using MATCH within the INDEX fn to come up the column number…within MATCH I’m using a modulo calculation.

I have a two dimensional table with a decade of factors so that once they hit the 11th duration it goes to the next line to pick up the next decade.

I can figure out the “row” part of it easy enough. For the column part I’m using this…
…where G3 is the duration.

Assume the table is what it is and there’s no changing it now.

I’m just wondering if there is a more elegant/clever way to find that column number?

This method works & it’s not a production-level work product…more scratch paper than anything…I’m just curious if there’s a better mouse trap out there.

If I am understanding correctly, think I have faced this before and did 1+mod(x-1,10). Still not elegant, but felt better than the “if” way.


Actually I think that is elegant, other than being able to change quadIO to 1.

1 Like

What are you some kind of high fallutin’ abstract calculus professor?

Around here we use right()+0.

Another solution, if you have cells to spare, is column = g3-row*10.

1 Like

If I’m understanding the problem correctly, wouldn’t the following do the trick?

= MOD($G3 - 1, 10) + 1

Edited to add: I see procrastinator has given lie to their name and beaten me to it. Yes, this is what I do too.