Excel Column Function
Column function in excel is used to find out the column numbers of the target cells in excel, it is also a built in worksheet function and takes only one argument which is the target cell as reference, note that this function does not give the value of the cell as it returns only the column number of the cell. In simple words, this excel column formula will provide a numeric value as output which signifies the column number of the supplied reference.
Syntax
Optional parameter:
- [Reference]: reference parameter is optional as if you did not supply the reference to this function, it will return the current column number where the function is located.
How to use Column Excel Function? (Examples)
Example #1
The column function provides the column number of the current cells where the column formula is located if the reference is excluded or omitted, as shown in the below table.
Example #2
If the range C5 is supplied to the column function in excel, then it will return the column number 3 as output.
Example #3
If a range is supplied to the column function as input, it will return the first column number as output for the below table, and the output will be 3.
Example #4
Column function with vlookup function in Excel.
The column function can be used with other functions. Here we are using this function with lookup function.
Suppose we an employee data containing ID, Name, and Salary column, and we have to find out the name from ID, then we can use the vlookup function combined with this function, as shown below.
=VLOOKUP(B11,$B$11:$D$15,COLUMN(C11),0)
Example #5
Column function with mod function.
Suppose a cable connection expense that is payable every third month and the cable connection expense amount is 500, and if we want to generate a fixed value every third month, then we can use column formula with the MOD function as shown below.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
The column formula used is =IF(MOD(COLUMN(B8)-1,3)=0,$A$2,0), as shown below:
Example #6
Suppose we need to get the address of the first cell in a supplied range so we can use the ADDRESS function with ROW and COLUMN. The column formula in excel to be used is below:
=ADDRESS(ROW(B24:D24)+ROWS(B24:D24)-1,COLUMN(B24:D24)+COLUMNS(B24:D24)-1)
This column formula in excel returns the ADDRESS of the first cell based on a row and column number. Here, we use the ROW function to generate a list of row numbers then add ROWS(B5: D5)-1 to ROW(B24: D24), so that the first item in the array is the last row number.
Then we do the same for COLUMN and COLUMNS: COLUMN(B24:D24)+COLUMNS(B24:D24)-1)
After applying this function, it will return an array of addresses. If we enter the column formula in a single cell, we just get the item ($D$24) from the array/range, which is the address corresponding to the last cell in a range, as shown in the below table.
Example #7
Suppose we have to find out the last column in a range, then we can min function in excel; the details are as follows:
=MIN(COLUMN(B23:D26))+COLUMNS(B23:D26)-1
As you know, if we provide a single cell as a reference to the COLUMN, it will return the column number as output for that particular reference/cell. However, when we supply a range that has multiple columns, this function will give an array as output that contains all column numbers for the given range. If we want to get only the first column number as output, we need to use the MIN function to get just the first column number, which will be the lowest number in the array. The result is shown below:
Point to Remember
- This function will return a #NAME! Error if we supply the invalid reference.
Recommended Articles
This has been a guide to the Column Function in Excel. Here we discuss the Column Formula and how to use it along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
- Column Lock in Excel
- Compare Two Columns in Excel for Matches
- Adding Columns in Excel
- Move Columns in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
