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.

Use the Fill Handle to AutoFill the rest of the values.

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 :