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

Use the MATCH function:

• Enter Pineapple as the search value in C12.

• To find the Row Number, enter this formula in C13.

• Press Enter.

• To find the Column Number, enter this formula in C14.

• Press Enter.
• This is the output.

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.

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

• Enter this formula in C13.

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

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.

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

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

• 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