Using an array function based on COLUMN function, you will be able to easily get a set of relative column numbers in a range.

Get relative column numbers in a range

You can easily get relative COLUMN number in a range using formula {=COLUMN(range)-COLUMN(range.firstcell)+1}. In this formula, COLUMN function is used twice.

Formula

{=COLUMN(range)-COLUMN(range.firstcell)+1}

Explanation and Example                  

In the example shown, the array formula in C4:J4 is: {=COLUMN(C4:J4)-COLUMN(C4)+1}

Below are some steps how this formula works:

The first COLUMN function ( COLUMN(range) )  produce an array of 8 numbers, as given below:

{3,4,5,6,7,8,9,10}

The 2nd COLUMN function ( COLUMN(C4) ) produce an array with one item, as given below

{3}

The above generated {3} well be subtracted in this step and will generate a new range, given below

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

This is the final step, in this step 1 will be added to yield below range:

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

Figure1. Example of getting relative column numbers in a range

You can adjust this formula to use a named range. For example, in the above example, if the named range “data” was created for C4:J4, you can use this formula to generate the column number:

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