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

Get FREE Advanced Excel Exercises with Solutions!

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. You will get a complete idea of how the COLUMN function works in Excel, both independently and with other Excel functions. In this article, I will show you how to use the COLUMN function in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


Introduction to COLUMN Function

Summary

The function returns the column number of a cell reference.

Syntax

The syntax or formula of the COLUMN function in Excel is,

=COLUMN([reference])

Syntax of The COLUMN Function in Excel

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.

Return

The function will return the number of a column based on a given cell reference.


4 Ideal Examples to Use COLUMN Function in Excel

In this article, you will see four ideal examples of how to use the COLUMN function in Excel. You will find how to directly use this function and how to combine this function with other Excel functions to get a certain value.

I will use the following sample data set to explain this article.

4 Ideal Examples to Use COLUMN Function in Excel


1. Determine Column Numbers

The basic application or use of the COLUMN function is to find out the column number or numbers of a given cell reference. From the following discussion, you will understand it better.

  • Firstly, look at the following image, where you will find the COLUMN function formula with different cell ranges as a reference.
  • I will discuss each formula in the following section.

Determine Column Numbers as An Ideal Example to Use COLUMN Function in Excel

  • First of all, the first formula returns the column number of the current cell. That is 3 for column C.
  • Secondly, the following formula will return the G10 cell’s column number which is 7.
  • Thirdly, you will be able to return the column number of the A4:A10 range which is 1 by using the third function.
  • Again, utilizing the fourth function, you can see the column numbers of the A4:F10 dynamic array which are 1 to 6.
  •  Lastly, the final formula of the above image returns the first column numbers of A4:F10 dynamic array which is 1.

2. Find First and Last Column Number of Any Range

By using the COLUMN function, you can find the first and last column numbers of any cell range. For that, you have to combine the COLUMN function with the MIN function to find the first column number and the MAX function to see the last column number. See the following steps for a better understanding.

Steps:

  • Firstly, to find the first column of a cell range, use the following combination formula in cell D13.
=MIN(COLUMN(C5:E11))

Inserting MIN and COLUMN Functions to Find First Column of Any Range as An Ideal Example to Use COLUMN Function in Excel

  • Secondly, after pressing Enter, you will see the desired column number that is 3.

  • Again, to see the last column number of the same cell range, in cell D15, insert the following combination formula.
=MAX(COLUMN(C5:E11))

Inserting MAX and COLUMN Functions to Find Last Column of Any Range as An Ideal Example to Use COLUMN Function in Excel

  • Finally, after pressing Enter, you can see the number of the last column of this cell range and it will be 5.

Read More: How to find text in an Excel range & return cell reference (3 ways)


3. Use as Dynamic Column Reference with VLOOKUP Function

In this example, you will see by using the COLUMN function how one can match data with given criteria. For performing this task successfully, you will need the help of the VLOOKUP function of Excel. Now let’s perform this procedure in the following steps.

Steps:

  • First of all, take the following data set, with all the necessary information.
  • Along with that, make three extra fields to show the result of this procedure.

  • Secondly, to make the formula easier for applying, I will make a dropdown list of the products of column C in cell B15.
  • For that, first choose cell B15 and then go to the Data tab of the ribbon.
  • After that, from the Data Tools group, select Data Validation.

  • Thirdly, from the Data Validation dialog box, make the dropdown style as List and give the appropriate cell range for creating the dropdown.
  • Lastly, press OK.

  • So, from the following image, you will be able to see the dropdown containing the products name.

  • Fifthly, to know the seller name of the particular product of cell B15, use the following combination formula in cell D15.
=VLOOKUP($B15,$C$5:$E$12,COLUMNS($C5:C5)+1,0)

Formula Breakdown

=VLOOKUP($B15,$C$5:$E$12,COLUMNS($C5:C5)+1,0)

  • Afterward, hit Enter and you will get the desired seller name.

  • Moreover, if you also want to find out the price of that product, insert the following formula in cell E15.
=VLOOKUP($B15,$C$5:$E$12,COLUMNS($C5:D5)+1,0)

  • Finally, press Enter and your job will be done.

  • Additionally, by changing the value of cell B15 you can get the result for your desired product.

Read More: How to use COLUMNS Function in Excel (3 Examples)


Similar Readings


4. Combine COLUMN Function with MOD and IF Function

Let’s say you have a dataset of the monthly bills of any organization. And you want to increase the bills by a specific number for every third month. You can perform this task by utilizing the IF, COLUMN, and MOD functions together. For doing that, see the following steps.

Steps:

  • In the beginning, see the following image with the monthly bills and I want to add $500 with every third month’s bill.

  • Secondly, in order to do that, write the following formula in cell C5.
=IF(MOD(COLUMN(C7)+1,3)=0,$F$4+C7,C7)

Combine COLUMN Function with MOD and IF Function as An Ideal Example to Use COLUMN Function in Excel

Formula Explanation

=IF(MOD(COLUMN(C7)+1,3)=0,$F$4+C7,C7)

  • Here MOD(COLUMN(B4)+1,3) finds every third month from the dataset.
  • $E$8+B4 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.
  • Thirdly, press Enter and you will find the same result as C5 in D5 as it is the first month.
  • In order to see the result for the entire row and all the columns, drag AutoFill in the right.

  • Finally, you will be able to add $500 with the values of every third month like the following picture.


Things to Remember

  • This function will deliver a #NAME! error if you provide an invalid reference in the argument.
  • In the fourth method, I created my data set from the second column. If your data set starts from another column then you have to modify the formula along with that change

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to understand how to use the COLUMN function in Excel. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.


Related Articles

Md. Abdullah Al Murad

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo