When we work on a large dataset, we often need to find row and column numbers of certain values. It is required to determine any specific information during work. But just scrolling left-right or up-down, takes a lot of time and energy and delays the process. For this reason, Microsoft Excel has given some solutions to find column numbers. In this article, we will discuss how to find column number based on a value in excel with 3 useful methods.
How to Find Column Number Based on Value in Excel: 3 Useful Methods
To describe the methods, here is a sample dataset. It shows the information of 6 types of fruits’ Sales Report with the information of Sold Items and Sales amount.
Now, let’s see how we can find any specific value’s column number with the following methods.
1. Use MATCH Function to Find Column Number in Excel
In this first method, we will use the MATCH function for finding the column number. Let’s see how it works.
- First, let us insert Pineapple as the search value in cell C12.
- Now, we will find the Row Number first.
- For this, insert this formula in cell C13.
=MATCH(C12,B:B,0)
- Afterward, hit Enter and you will see the Row Number of the Search Value.
- Finally, we will find the Column Number now.
- To perform this, insert this formula in cell C14.
=MATCH(C12,8:8,0)
- Lastly, press Enter.
- That’s it, you will see that Pineapple is located in Column Number 2.
Read More: How to Return Column Number of Match in ExcelÂ
2. Get Column Number Based on Value with COLUMN Function
In this second method, let us apply the COLUMN function to get the column number. Follow the steps below.
- In the beginning, insert $4,500 as the Search Value in cell 12.
- Then, click on this value from the dataset.
- Afterward, you will see that cell number D8 is showing in the Name Box.
- Now, insert this formula into cell C13.
=COLUMN(D8)
- Lastly, hit Enter.
- That’s it, you will see the Column Number is 4 for the Search Value.
Read More: How to Find Column Index Number in ExcelÂ
3. Excel VBA to Search Column Number Based on Cell Value
This last method will help you to search the column number with the Excel VBA Marco code. VBA is a programming language for Excel and is widely used for fast and versatile output. Let’s see the process below.
- First, select the cell that you want as the search value.
- For example, here we selected the value 25 which is in cell C7.
- Now, go to the Developer tab and select Visual Basic from the Code group.
- Then, select Module from the Insert section in the Visual Basic window.
- Now, insert this code on the blank page.
Sub GetColumnNumber()
colNumber = Range("C7").Column
MsgBox "Column Number: " & colNumber
End Sub
- Following, go to the Run tab and click on Run Sub or press F5 on your keyboard.
- Lastly, click on Run in the Macro window.
- Finally, you will see Column Number 3 in a Message Box for the selected cell.
Read More: Find Value in Row and Return Column Number Using VBA in Excel
Things to Remember
- The reference argument in the COLUMN function can never be in multiple areas. Otherwise, it will show an error.
- In case you are using any other version of Excel except Microsoft 365, the functions used in this article will perform as an array formula. Therefore, you will need to press Ctrl + Shift + Enter to confirm it.
Download Practice Workbook
Conclusion
Henceforth, we have come to the end of our article on how to find column number based on a value in excel with 3 useful methods. Let us know if you know any other methods to find column numbers.
