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.

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

• Enter Pineapple as the search value in C12.

• 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 B.Â  0 returns an Exact Match.
• To find the Column Number, enter this formula in C14.
`=MATCH(C12,8:8,0)`

• Press Enter.
• This is the output.

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

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.

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

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

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

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.

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

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

Things to Remember

• The reference argument in the COLUMN function can never be in multiple areas. Otherwise, it will show an error.

Related Articles

<< Go Back to Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF