# Excel function: Select range from current cell -> end of column in a table

Here is my Excel table:

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)
↓↓↓
=MATCH("THEN",\$A\$5:\$A\$2:\$A\$12,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 :)