Naimul Hasan Arif
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.
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.
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.
- Use Fill Handle to AutoFill the rests.
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
- How to Count Columns until Value Reached in Excel
- Excel VBA: Count Columns with Data (2 Examples)
- How to Count Columns for VLOOKUP in Excel (2 Methods)
- How to Count Number of Columns in Excel (3 Easy Methods)
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 :
- 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.
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.
