How to Find Column Index Number in Excel

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.

dataset column index number formula


Method 1

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.

file tab column index number formula

  • Next, At the left-down corner, you will find Options.

options column index number formula

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

reference style column index number formula

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

column number column index number formula

📕 Read More: Count One Column If Another Column Meets Criteria in Excel


Method 2

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)

column function column index number formula

  • Finally, Press Enter to see the Column Index Number in the Cell.

column number final dataset column index number formula

  • Likewise, you can follow the same step for the next one as well.

column function 2 column index number formula

  • Subsequently, you have your column Index number just like before.

column function 2 final column index number formula

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)

double column 1 column index numbers formula

  • Hit Enter and you will see your column numbers have been perfectly reorganized.

double coumn 2 column index numbers formula

  • Finally, drag to your left or use Fill Handle to Copy the formula in the entire Row.

double cloumn final column index numbers formula

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


Method 3

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)

match function column index numbers formula

  • Press Enter and Excel will show you the column number.

match function result column index numbers formula

  • To conclude, drag to copy the formula to the entire column.

match function drag column index numbers formula

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.


Related Articles

(Visited 77 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo