Exceljet

Quick, clean, and to the point

Get relative column numbers in range

Excel formula: Get relative column numbers in range
Generic formula 
{=COLUMN(range)-COLUMN(range.firstcell)+1}
Explanation 

To get a full set of relative column numbers in a range, you can use an array formula based on the COLUMN function.

In the example shown, the array formula in B4:H4 is:

{=COLUMN(B4:H4)-COLUMN(B4)+1}

On the worksheet, this must be entered as multi-cell array formula using Control + Shift + Enter

This is a robust formula that will continue to generate relative numbers even when columns are inserted in front of the range.

How this formula works

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}

In other array formulas

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

 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.

Just a brief view of your website, and one specific idea - a "helper" value column - allowed me to do in less than two hours something I had been going down dead-ends with for two days. Thank you, thank you, thank you! -Roberta
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course