3 Ways to Find Column Index Number in Excel

Often we have to work with datasheets with numerous columns. I am frequently asked the question that how to find the column index number in a worksheet. Now, what is Column Index Number? In a simplified way, it is the position number of a column in a referred data table. Now, I’m going to show you 3 easy means to find a column index number in Excel.

Introduction to the Dataset

Before going to the solutions, first, let me introduce you to our dataset for this article. We have some details of a real-estate company here with its Invertor names, their property names and a few other pieces of information.

Easiest Way to Find Column Index Number in Excel

Using the Match function is a handy approach for finding a column index number in Excel.

Formula Syntax ↓

=MATCH(lookup_value, lookup_array, [match_type])

Now Follow The Guide ↓

Step_1: Insert two new columns in an empty place beside the data table.

I added two columns- Column Name (column F) and Column Index (column G).

Step_2: Input the column header names from the main table into the Column Name (column F).

Step_3: Select the entire dataset (click on a cell and press CTRL+A).

Step_4: Click on the Insert tab from the ribbon.

Step_5: Under the Tables group, hit the Table command.

Then the Create Table dialog box will pop up.

Step_6: Make sure the My table has headers checkbox is checked.

Step_7: Hit the OK button.

This will give the table a Table Name. The Table Name will appear on the top-left corner of the ribbon.

The table of my worksheet is named Table3.

Step_8: Type this formula inside the cell G2:

=MATCH(F2,Table3[#Headers],0) 

Step_9: Press ENTER to apply the formula.

Step_10: Take your mouse cursor to the lower bottom corner of cell G2, and the cursor will turn into Fill Handle.

Step_11: Double click on the Fill Handle.

Final Result ↓

All the column index numbers will come along according to their column names immediately.

🔗 2 Ways to Count Columns in Excel for Vlookup

🔗 [Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)

Alternative Method #1: Use the COLUMN Function to Find Column Index Number in Excel

In this method, I am going to show how can use find column index numbers by applying the COLUMN function.

Formula Syntax ↓

=COLUMN([reference])

Not to mention that I am going to use the previous datasheet here. The first few steps are like the previous method.

Now Follow The Guide ↓

Step_1: Insert a new table in the same sheet with two columns.

The column headers are Column Name (column A) and Column Index (column B).

Step_2: Insert the column header names from the main table into Column Name (column A).

Step_3: Select the entire dataset (CTRL+A).

Step_4: Hit the Insert tab.

Step_5: Click on the Table command from the Tables group.

The Create Table dialog box will appear on the screen.

Step_6: Make sure the My table has headers checkbox is checked.

Step_7: Hit the OK button.

The Table Name will appear on the top-left corner of the ribbon.

Table1 is the name of my selected data table.

Step_8: Now copy this formula inside the cell B12:

=COLUMN(Table1[[#Headers],[Worth]])

Step_9: Repeat writing the formula in the rest of the columns according to the header names.

Formulas for the columns Property Name, Investor Name, & Property ID respectively.

=COLUMN(Table1[[#Headers],[Property Name]])

=COLUMN(Table1[[#Headers],[Investor Name]])

=COLUMN(Table1[[#Headers],[Property ID]])

Final Result ↓

This way you can find out any column index number easily. All you have to do is input the header names manually.

🔗 [2 Cases] Count Columns in Excel Until a Certain Value Reached

🔗 3 Methods to Convert Column Number to Letter in Excel

Alternative Method #2: Use the VLOOKUP Function to Find Column Index Number in Excel

Before getting into the method direction, I would like to inform you that, this method only works for the data tables which start in column A. VLOOKUP function is a handy way to find the column index numbers. All you have to do is recall the syntax of its formula.

Formula Syntax ↓

=VLOOKUP(lookup_array, table_array, column_index_number, [range_lookup])

Suppose, I am trying to figure out the column index number of header Worth (column D).

Now Follow The Guide ↓

Step_1: Select the whole data table.

Step_2: Click on the Insert tab.

Step_3: Under the Tables group, hit the Table option.

The Create Table dialog box will pop up on the sheet.

Step_4: Click on the OK button.

It gives the table, a Table Name.

Step_5: Put your mouse cursor in an empty cell.

I have selected cell F2 from a spare column named Position (column F).

Step_6: Insert the formula in cell F2:

=VLOOKUP(A2,Table2,COLUMN(Table2[Worth]),0)

Step_7: Press ENTER to apply the formula.

Step_8: Double-click on the cell value.

The applied VLOOKUP formula appears instantly with the syntax below it.

Step_9: Take your mouse cursor upon col_index_num in the syntax and click on it.

This will select the portion of the column index number from the applied formula.

Step_10: Press F9.

Final Result ↓

This way the col_index_num will show the index number of our selected column.

🔴 Note. If the F9 key from your keyboard does not give the result of the column index number, press Fn+F9 together.

🔗 Count Rows in Excel Until a Certain Value Reached [2 Cases]

🔗 6 Ways to Count One Column in Excel If Another Column Meets Criteria

Conclusion

I think now you have a clear idea about what a column index number is and how you can find the column index number from an Excel sheet. Feel free to leave a comment if you have any confusion about this article. Let us know how you like your website. Thank you.