3 Ways to Find Column Index Number in Excel Vlookup

Often we have to work with datasheets with numerous columns. I am frequently asked the question of how to find the column index number in a worksheet while using vertical lookup? Now, what is Column Index Number in Excel in case of vlookup or vertical lookup? 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 vlookup.

What is the Column Index Number in Excel VLookup?

In both the VLOOKUP function and the INDEX-MATCH combination, the concept of a “column index number” remains the same. The column index number represents the column from which you want to retrieve data in a vertical lookup. Whether you use VLOOKUP or INDEX-MATCH, this number is essential to specify the location of the data you’re interested in.

Here’s how it works in both scenarios:

A. Using VLOOKUP Function

In the VLOOKUP function, the column index number is explicitly provided as the third argument (col_index_num).

This number indicates which column in the table_array (the range where you’re searching for data) contains the data you want to retrieve.

For example, if you want to retrieve data from the third column of the table_array, you would set the col_index_num to 3.

=VLOOKUP(lookup_value, table_array, 3, FALSE)

B. Using INDEX-MATCH Combination

In the INDEX-MATCH combination, you don’t specify the column index number directly as you do in the VLOOKUP function.

Instead, you use the MATCH function to find the position of the column you want to retrieve data from based on a header or criteria.

The MATCH function returns the relative position (column number) of the header or criteria within a row or range.

This position is then used as the column index number in the INDEX function to retrieve the desired data.

=INDEX(table_array, MATCH(lookup_value, lookup_column, 0), 3)

In the INDEX-MATCH example above, MATCH(lookup_value, lookup_column, 0) returns the relative position of the lookup_value within the lookup_column.

This position (which is the same as the column index number in the table_array) is used in the INDEX function to specify which column in the table_array to retrieve data from.

So, whether you use VLOOKUP or INDEX-MATCH, the column index number represents the location of the data you want to extract in a vertical lookup operation within 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 Vlookup

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

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

Usage 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) 

Formula Explanation

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

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

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.

Usage 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]])

Formula Explanation

• 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 that start in column A. The VLOOKUP function is a handy way to find the column index numbers. All you have to do is recall the syntax of its 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).

Usage 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)

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

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.