How to Use COLUMN Function in Excel (4 Easy Examples)

Overview of Column function

Sometimes we may need to figure out or determine the column number of any specific cell. For this purpose, Excel provides a function named COLUMN. This function returns the column number of any reference cell. This article will share the complete idea of how the COLUMN function works in Excel independently and then with other Excel functions.

Download the Practice WorkBook

COLUMN Function in Excel (Quick View)

Overview of Column function

Excel COLUMN Function: Syntax & Arguments

Syntax of column function

Summary

The function returns the column number of a reference.

Syntax

=COLUMN([reference])

Arguments

Arguments

Argument Required or Optional Value
[reference] Optional The cell or range of cells for which we want to return the column number. If the reference argument refers to a range of cells, and if the COLUMN function is entered as a horizontal array formula, the COLUMN function returns the column numbers of reference as a horizontal array.

Note: 

  • In OFFICE 365, we can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula.
  • Another way is to enter it as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. You can confirm it by seeing brackets at the beginning and end of the formula.

How to Use the COLUMN Function in Excel (4 Examples)

Example 1: VLOOKUP with Dynamic Column Reference Using COLUMN Function

This COLUMN function is mostly used as an argument of another popular function named the VLOOKUP function. Let’s see how we can use this. For this let’s consider we have a dataset of some products with their ID, Product, Seller, and Price. Now we will find the seller and price automatically by using only the product name.

VLOOKUP with Dynamic Column Reference Using COLUMN Function

Step 1: Enter the below formula in cells C14 and D14

=VLOOKUP($B14,$B$4:$D$11,COLUMNS($B4:B4)+1,0)

And

=VLOOKUP($B14,$B$4:$D$11,COLUMNS($B4:C4)+1,0)

Formula Explanation

Enter formula in cell D14

Enter formula in cell D14

[ Note: Here in cell C14, the #NA error is showing as we didn’t enter any input value in cell B14.It will be changed when we will put any value on that cell.]

Step 2: Enter any product name from the drop-down list and press Enter

Output of VLOOKUP and COLUMN function

Example 2: Formula with COLUMN and MOD function

Let’s say we have a dataset of the monthly bills of any organization. But we want to increase the bills by a specific number for every third month. The scenario will be like this:

Formula with COLUMN and MOD function

Step 1: Enter the formula in cell B5 and copy it dynamically by hovering your mouse to the right side

=IF(MOD(COLUMN(B4)-1,3)=0,$E$8+B4,B4)

Formula Explanation

  • Here MOD(COLUMN(B4)-1,3) this portion finds every third month from the dataset.
  • $E$8+B4 this will add the current bill with the input bill if the condition is true.
  • B4 is used if the condition is false, it will print the previous bill.

Enter the formula with mod and column function

Step 2: Enter the amount that you want to add, and press Enter

Enter amount and press Enter

Example 3: Find the First Column Number of any Range

Now let’s see the column comparison with this function. Let’s have a dataset of some salespersons with their three months sales. Now we will find out the first column number from the range of sales.

Find the First Column Number of any Range

Step 1: Enter the formula in cell D13 and press Enter

=MIN(COLUMN(C5:E11))

Formula Explanation

  • C5:E1 this is the range of all columns with values. COLUMN(C5:E11) will return the column references.
  • Then MIN function defines the finds the minimum values from the given dataset.

Enter formula using MIN and column function

Example 4: Find the Last Column Number of any Range

Now here we will find out the last column number of the sales range. The dataset will be the same, but the formula will be different.

Find the Last Column Number of any Range

Step 1: Enter the formula in cell D13 and press Enter

=MAX(COLUMN(C5:E11))

Enter formula using MAX and COLUMN function

Things to Remember

Common Errors       When they show
#NAME! This function will deliver a #NAME! Error if we provide the invalid reference in the argument.

Conclusion

This is all about the COLUMN function and its different applications. Overall, in terms of working with time, we need this function for various purposes. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any other method of utilizing this function, then please feel free to share it with us.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo