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


Download Practice Workbook


2 Easy Ways to Find Column Index Number in Excel

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: Perform VLOOKUP by Using Column Index Number from Another Sheet


Similar Readings


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.

Read More: How to Find Column Index Number in Excel VLOOKUP (2 Ways)


Practice Section

For further expertise, you can practice here.

How to Find Column Index Number in Excel


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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo