# 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])`

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) ` Formula Explanation
• F2 is my specified cell address.
• Table3 mentions the table name.
• 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.

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]])` Formula Explanation
• Table1 is the mention of the selected date range.
• 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).

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)` Formula Explanation
• 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