Get relative column numbers in range

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.



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:


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


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


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


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:


Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment