Here is my Excel table:

Table with operators

I am trying to calculate the "Offset of THEN" by using this formula:

=MATCH("THEN",[@[Rule operator]]:[Rule operator],0)

The formula above doesn't work, but this one does:

=MATCH("THEN",[@[Rule operator]]:$A$12,0)

The problem with the latter one is that it stops working whenever the index of the last row changes.

How to select a range from current cell to the end of the column in an Excel table?


Your formula doesn't work because [Rule operator] is a range itself, so if your formula is entered in a cell on row 5, Excel will evaluate your formula like this:

=MATCH("THEN",[@[Rule operator]]:[Rule operator],0)

Because A5 is in the middle of the range A2:A12, Excel ignores it and just uses A2:A12.

To address this you can keep the [@[Rule operator]] bit but you need something to work out where the last cell in your column is.

You can do this by counting the number of rows in your table using ROWS([Rule operator]), adding 1 for your header row (add more if you have other rows above your table), then using the INDIRECT function to assemble that into a cell reference. This gives you:

=MATCH("THEN",[@[Rule operator]]:INDIRECT("A"&1+ROWS([Rule operator])),0)

Because this will output a MATCH row position including the row you are running the formula from, you'll need to subtract 1 to calculate the Offset of THEN value you need for each row:

=MATCH("THEN",[@[Rule operator]]:INDIRECT("A"&1+ROWS([Rule operator])),0)-1

That's it :)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.