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

Question

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?

Answer 1

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 :)