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}`

## Leave a Comment