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.


Download Practice Workbook

Download this practice file and try it by yourself.


3 Useful Methods to Find Column Number Based on Value in Excel

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.

3 Useful Methods to Find Column Number Based on Value in Excel


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.

Use MATCH Function to Find Column Number in Excel

  • 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)

Use MATCH Function to Find Column Number in Excel

  • 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.

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


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.

Get Column Number Based on Value with COLUMN Function

  • 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)

Get Column Number Based on Value with COLUMN Function

  • 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.

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


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.

Excel VBA to Search Column Number Based on Cell Value

  • 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

Excel VBA to Search Column Number Based on Cell Value

  • 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: How to Count Columns until Value Reached 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.

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. Keep an eye on ExcelDemy for more exciting and useful articles on excel.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo