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)
- F2 is my specified cell address.
- Table3 mentions the table name.
- #Headers selected the column headers of Table3.
- 0 is the Match Type for the exact match.
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]])
- Table1 is the mention of the selected date range.
- #Headers select the column headers of Table1.
- Worth is a sample column header name. The above formula returns the column index number of the column “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)
- A2 is the first cell address of the data table.
- Table2 is the name of the table.
- Worth is my targeted column.
- 0 is for the exact match.
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.