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

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we face the necessity of finding the Index number of columns while working in Excel. Here, we will try to explain some ways how to find Column Index number in Excel.

For simplification, we are going to use a dataset containing the Painting Name, Painter, and Period columns.

How to Find Column Index Number in Excel


How to Find Column Index Number in Excel: 2 Methods

1. Using MATCH Function to Find Column Index Number

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

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

The MATCH function parameters are:

  • lookup_value – a value that needs to find in the lookup_array
  • lookup_array – the array where to find a value
  • [match_type] – a type of match. Here, we put 0 which is an exact match.

Steps :

  • Select the whole area containing data. Here, I selected B4:D11.
  • Select Table from Insert Tab.

Alternatively, we can press CTRL + T to create a Table.
A dialogue box will appear.

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

How to Find Column Index Number in Excel

The table will be created.

  • Pick a location where you want to find the column index. Here, I created a Table named Table3 with Column Name and Column Index titles.

  • Employ the formula of MATCH function in cell C15.
=MATCH(B15,Table3[#Headers],0)

Here, B15 is the lookup value which means a value that we want to find in the lookup_array.Table3[#Headers] is the lookup_array where to find the value. I used 0 to find the exact match.

  • Press ENTER and the Column Index Number will be shown.

How to Find Column Index Number in Excel

The most amazing part of the MATCH function is that it is applicable for all datasheets. We just need to mention the table name.

We can apply it for the rests by Fill Handle.

Read More: How to Return Column Number of Match in Excel 


2. Applying COLUMN Function to Find Column Index Number

Implementation of COLUMN Function is another way to find Column index numbers in Excel. In this method, we will find the Column index number according to the built-in Excel Sheet Column Number.

The function here is:
COLUMN([reference)] Where reference means the mentioned column whose index number needs to be found.

Steps :

  • Put the COLUMN function where we want to find the value.
  • Here, I selected the C15 cell to input the formula of COLUMN function and selected B4  as the reference.

The function follows :

=COLUMN(Table2[[#Headers],[Painting Name]])

How to Find Column Index Number in Excel

  • Press ENTER and we will have the result according to the built in Excel Sheet Column Number.


Practice Section

For further expertise, you can practice here.

How to Find Column Index Number in Excel


Download Practice Workbook


Conclusion

Finding Column index number easily is the only purpose of this article. From this article, you will get to know how to find column index number in Excel. You can comment below for further information.


Related Articles


<< Go Back to Column Number | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly 2 years. Currently serving as an Excel and VBA Content Developer, Arif has authored over 120 articles. His expertise lies in Microsoft Office Suite, and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and ongoing enthusiasm for expanding his knowledge in data analysis.

Designation

Excel &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo