Get relative column numbers in range

The first COLUMN function generates an array of 7 numbers like this:

{2,3,4,5,6,7,8}

The second COLUMN function generates an array with just one item like this:

{2}

which is then subtracted from the first array to yield:

{0,1,2,3,4,5,6}

Finally, 1 is added to get:

{1,2,3,4,5,6,7}

With a named range

You can adapt this formula to use with a named range. For example, in the above example, if you created a named range "data" for B4:H4, you can use this formula to generate column numbers:

{=COLUMN(data)-COLUMN(INDEX(data,1,1))+1}

You'll encounter this formula in other array formulas that need to process data column-by-column.

With SEQUENCE

With the SEQUENCE function the formula to return relative row columns for a range is simple:

=SEQUENCE(COLUMNS(range))

The COLUMNS function provides the count of columns, which is returned to the SEQUENCE function. SEQUENCE then builds an array of numbers, starting with the number 1.  So, following the original example above, the formula below returns the same result:

=SEQUENCE(COLUMNS(B4:H4)) // returns {1;2;3;4;5;6;7}

Note: the SEQUENCE formula is a new dynamic array function available only in Excel 365.