Column Function in Excel

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 ExcelVlookup Function In ExcelThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more.

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.

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 functionADDRESS FunctionThe address function finds the cell's address and returns an absolute value. It requires two mandatory arguments: the row number and the column number. For example, if we use =Address(1,2), the result will be $B$1.read more 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 functionWe Use The ROW FunctionThe row function in Excel is a worksheet function that displays the current row index number of the selected or target cell. The syntax to use this function is as follows: =ROW( Value ).read more 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 excelMin Function In ExcelIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array.read more; 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