How to Find a Column Number Based on a Value in Excel – 3 Methods

This is a sample dataset. It is a Sales Report with information on Sold Items and Sales amount.

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

 

Method 1 – Using the MATCH Function to Find the Column Number in Excel

Use the MATCH function:

  • Enter Pineapple as the search value in C12.

Use MATCH Function to Find Column Number in Excel

  • To find the Row Number, enter this formula in C13.
=MATCH(C12,B:B,0)

  • Press Enter.

The MATCH function searches for the value in C12 in column B0 returns an Exact Match.
  • To find the Column Number, enter this formula in C14.
=MATCH(C12,8:8,0)

Use MATCH Function to Find Column Number in Excel

  • Press Enter.
  • This is the output.

The MATCH function searches for the value in C12 in row 8. 0 returns an Exact Match.

Read More: How to Return Column Number of Match in Excel 


Method 2 – Getting the Column Number Based on a Value with the COLUMN Function

Use the COLUMN function:

  • Enter $4,500 as the Search Value in C12.

Get Column Number Based on Value with COLUMN Function

  • Click this value.
  • D8 is showing in the Name Box.

  • Enter this formula in C13.
=COLUMN(D8)

Get Column Number Based on Value with COLUMN Function

  • Press Enter.
  • The Column Number is 4 for the Search Value.

The COLUMN function is used to perform as a lookup and find the column number for the reference cell: D8.

Read More: How to Find Column Index Number in Excel 


Method 3 – Using Excel VBA to Search a Column Number Based on a Cell Value

  • Select the cell you want as the search value, here 25 in C7.

Excel VBA to Search Column Number Based on Cell Value

  • Go to the Developer tab and in Code, select Visual Basic.

  •  In Insert, choose Module.

  • Enter this code.
Sub GetColumnNumber()
    colNumber = Range("C7").Column
    MsgBox "Column Number: " & colNumber
End Sub

Excel VBA to Search Column Number Based on Cell Value

  • Go to the Run tab and click Run Sub or press F5.

  • Click Run in the Macro window.

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

Download Practice Workbook


Related Articles


<< Go Back to Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria
Sanjida Mehrun 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... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo