Md. Abdullah Al Murad
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])
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.
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.
- 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))
- 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))
- 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)
- Here $B14 is the input field. I will enter the input in this field.
- $B$4:$D$11 is the table range where the data is stored.
- COLUMNS($B4:B4)+1 this portion of the formula will return the Seller column values.
- Defining 0 as range_lookup we are considering the exact match for the comparison.
- Are you interested in exploring this VLOOKUP function? Try these links:
1. How to Get the Max Value Using VLOOKUP in Excel
2. VLOOKUP and HLOOKUP combined Excel formula (with example)
3. VLOOKUP Formula to Compare Two Columns in Different Sheets!
4. Using VLOOKUP with IF Condition in Excel (5 Real-Life Examples)
- 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
- How to Use INDIRECT Function in Excel (12 Suitable Instances)
- Use OFFSET Function in Excel (3 Examples)
- Offset(…) Function in Excel with Examples
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)
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.
