How to Find Column Number Based on Value in Excel

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.

Here, we applied the MATCH function to search for the value in cell C12 from column B. Along with it, we typed 0 to get an Exact Match.

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)

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: " & colNumberEnd 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

Find Column Number Based on Value.xlsm

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.

Column Letter to Number Converter in Excel
How to Convert Column Number to Letter in Excel
How to Change Column Name from ABC to 1 2 3 in Excel
VBA to Convert Column Number to Letter in Excel
How to Use VBA Range Based on Column Number in Excel

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

Get FREE Advanced Excel Exercises with Solutions!

How to Find Column Number Based on Value in Excel - ExcelDemy (2024)