How to Find Column Index Number in Excel (2 Methods)

In this tutorial, we will explain some ways to find the Column Index Number in Excel.

We’ll use the following dataset containing the Painting Name, Painter, and Period columns to illustrate our methods.

Method 1 – Using the MATCH Function to Find the Column Index Number

The MATCH Function is the best way to find a column index number.

This function works as follows:
`MATCH(lookup_value, lookup_array, [match_type])`

The MATCH function parameters are:

• lookup_value – the value to find in the lookup_array
• lookup_array – the array in which to find the lookup_value.
• [match_type] – the type of match. 0 is for an exact match and 1 for a partial match.

Steps :

• Select the whole dataset, B4:D11.
• Select Table from the Insert tab. Or, press CTRL + T to create a Table.

A dialog box will appear.

• Select the Range of the Table.
• Click OK.

The table will be created.

• Pick a location to store the returned column index number. For this purpose, we created a Table named Table3 with Column Name and Column Index titles.

• Enter the following formula in cell C15:
`=MATCH(B15,Table3[#Headers],0)`

Here, B15 is the lookup value, Table3[#Headers] is the lookup_array, and we set the match_type to 0 to find an exact match.

• Press ENTER to return the Column Index Number.

A very useful feature of the MATCH function is that it can take references in all the datasheets. We just need to specify the table name.

• Apply the formula to the rest of the cells using the Fill Handle.

Method 2 – Using the COLUMN Function to Find the Column Index Number

The COLUMN function is another way to find Column index numbers in Excel. We’ll find the Column index number according to the built-in Excel Sheet Column Number.

The function here is:
```COLUMN([reference)] ```Where reference is the column whose index number is sought.

Steps :

• In cell C15, enter the following COLUMN function with cell B4 as the reference:

• Press ENTER to return the column index number.

Related Articles

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF