As you know, an Excel sheet consists of Rows and Columns. If you have a large dataset, it might get difficult for you to keep track of your data. You’ll want to know in which column your data are. Hence comes the necessity to find the column index number in Excel. This article will provide you with fresh ways to quickly get column index number.
📁 Download Excel File
You can download this workbook for a better understanding.
Learn to Find Column Index Number in Excel with These 3 Unique Methods
In this article, we’ll be discussing three different ways you can find the Column Index Number. Furthermore, we’re going to use a dataset containing Students with their obtained numbers in different subjects and their relative positions. Have fun learning the tricks.
1. Changing Column Labels into Numbers
By default, Excel Labels the columns with alphabets. If only Excel would Label the columns with numbers just like they did to Rows. Then, we wouldn’t have to think about finding the column index number in Excel at all. It would be just in front of our sight.
Certainly, there is a way we can modify our column header alphabets by changing them into numbers. Let’s find out how.
⬇️⬇️ STEPS ⬇️⬇️
- First, go to the File It will show Excel’s Backstage view.
- Next, At the left-down corner, you will find Options.
- Following this, click on Options and a dialogue box will appear.
- Now go to Formulas.
- You will find a Tab Working with formulas and under the Tab, check the box containing the R1C1 reference style.
- Click Ok to apply the change and you will see your columns name change into numbers. Now you can see the Column index number whenever you want.
📕 Read More: Count One Column If Another Column Meets Criteria in Excel
2. Using COLUMN Function
Sometimes you don’t want to change the interface of your Excel workbook and also sometimes you’ll have to show your data’s column index number in another cell. How do we do that? Glad you asked. We will use the COLUMN function. But first, let’s know a little about the function and how it works.
Syntex of the Function:
COLUMN(your cell reference no of whose column number you want to get)
Suppose you want to show the column index number where “Students Name” and “Position” reside. Let’s do that using the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, Select the cell where you want to show your column index number. We have selected I5.
- Now Put write this formula in the Formula Bar. Note that, B5 is the cell where “Students Name” is.
=COLUMN(B5)
- Finally, Press Enter to see the Column Index Number in the Cell.
- Likewise, you can follow the same step for the next one as well.
- Subsequently, you have your column Index number just like before.
However, there is a problem that comes with this formula. Here, as you can see Excel starts counting the column index number from A1, but what if you want to want to start counting your column number from the cell you first put your data into? Definitely, there is a solution for that as well. We can use the following way.
⬇️⬇️ STEPS ⬇️⬇️
- Go to the cell you want to showcase your column index number. In this example, we are selecting B12.
- Next, type the below-mentioned formula.
=COLUMN()-COLUMN($A$5)
- Hit Enter and you will see your column numbers have been perfectly reorganized.
- Finally, drag to your left or use Fill Handle to Copy the formula in the entire Row.
Let’s understand what we just did here. We put COLUMN() so that Excel understands from where It needs to start counting and COLUMN($A$5) is the previous cell reference whose column index number will be subtracted every time we get to a new cell. Again, to make it unchangeable we put $ in between cell numbers which are called Absolute cell references.
📕 Read More: Count One Column If Another Column Meets Criteria in Excel
3. Employing MATCH Function
While working with a large Table dataset, you might not want to think about the formula for counting column index numbers. In fact, we can automate the process by using the MATCH function.
Syntex of MATCH function:
MATCH(lookup_value, lookup_array, [match_type])
The MATCH function requires three parameters:
Lookup_value– The value that Excel will search for in the lookup array/range.
Lookup_array– The table or the range of values where your lookup value resides.
Match_type– There are three match types
1 for getting values less than the lookup value
0 for the exact match
-1 for getting values greater than the lookup value.
Now, let’s find the column index number for our table. We want to get the column index from the first table based on the “Column name” in Column H.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select Cell I8 and put the following formula in the Formula Bar.
=MATCH($H9,Table1[#Headers],0)
- Press Enter and Excel will show you the column number.
- To conclude, drag to copy the formula to the entire column.
Unlike the COLUMN function, MATCH returns the relative position of the cell H9 (Column name) in the Table 1 header range. The result is 1 since ‘Students Name’ is in the first column of the Table.
📕 Read More: 2 Ways to Count Columns for VLOOKUP in Excel
📄 Important Notes
🖊️
While using the COLUMN function, don’t use Fill Handle. You will have to put the cell references individually.
🖊️
The MATCH function only works for Tables.
🖊️
The table works differently from regular cell ranges when it comes to lookup_array.
🖊️
Put a $ sign in your cell number while using the Fill handle if you don’t want your lookup_value to change.
📝 Takeaways from This Article
📌
We explained three methods to find the Column Index number in Excel.
📌
Readers will learn two new simple functions to Find the Column Index number.
📌
Readers will understand more about how the interface of Excel works.
Conclusion
That’s it. I hope from now on you will be able to find the Column Index number easily while working in Excel. If you have any questions, feel free to comment below and for more cool articles about Excel, you are always welcome to visit Excelden.com. Thank you.