Excel Column Function Formula Examples How To Use

Excel Column Function

For example, the formula COLUMN(A4) returns 1 since COLUMN A is the first column in the Excel worksheet.

Syntax

Optional parameter:

[Reference]: The reference parameter is optional. If we do 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

As shown below, the column function provides the column number of the current cells where the column formula is located if the reference is excluded or omitted.

Example #2

If the range C5 is supplied to the column function in Excel, it will return 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. The output will be 3.

Example #4

The 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.

We can use the column function with other functions. For example, we are using this function with the lookup function here.

Suppose we have employee data containing ID, name, and salary column. And, we have to find out the name from the 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

The column function with MOD function.

Suppose a cable connection expense that is payable every third month. The cable connection expense amount is $50. We can use the column formula with the MOD function below if we want to generate a fixed value every third month.

The column formula used is =IF(MOD(COLUMN(B8)-1,3)=0,$A$2,0), as shown below:

Example #6

Suppose we need to obtain the address of the first cell in a supplied range. So, here, 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 and 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 can return an array of addresses. For example, if we enter the column formula in a single cell, we get the item ($D$24) from the array/range, the address corresponding to the last cell in a range, as shown below.

Example #7

Suppose we have to find out the last column in a range, then we can use the 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

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 with multiple columns, this function may give an array as output containing all column numbers for the given range. Therefore, if we want to get only the first column number as output, we must use the MIN function to get just the first column number, 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.