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.

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.

In this formula, the MATCH function is used to search for the value in cell C12 from row 8. Also to get an Exact Match, we enter 0.

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.

Here, the COLUMN function is used to perform as a lookup function. It helps to find the column number of the reference cell D8 in the dataset.

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.

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.

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

Get FREE Advanced Excel Exercises with Solutions!