Return column number of a specific value

To return a column number of a specific value we need to apply the Excel MATCH function

Example: Return column number of a specific value

METHOD 1. Return column number of a specific value

EXCEL

=MATCH(C6,4:4,0)

The formula uses the Excel MATCH function to return the column number for a specific value ("Cereal") that is captured in row 4. The formula will only return the column number of the first occurrence of the specific value.

METHOD 1. Return column number of a specific value using VBA

VBA

Sub Return_column_number_of_a_specific_value()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'apply the formula to return the column number of a specific value held in row 4
ws.Range("E5").Value = Application.WorksheetFunction.Match(ws.Range("C6"), ws.Range("4:4"), 0)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Specific Value: Have the specific value that you are searching for in cell ("C6").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Row Reference: Select the row(s) that you are searching through for the specific value by changing the row reference ("4:4") in the VBA code to any row in the worksheet, that doesn't conflict with the formula.
Specific Value: Select the specific value that you are searching for by changing the cell reference ("C6") in the VBA code to any cell that contains the specific value that you wan to search for in the Analysis worksheet that doesn't conflict with the formula. You can also change the value that is held in cell ("C6") to any specific value that you are looking to return the column number for.

Explanation about the formula used to return column number of a specific value

EXPLANATION

EXPLANATION
To return a column number of a specific value we need to apply the Excel MATCH function.

FORMULAS
=MATCH(lookup_value, row_ref, 0)

ARGUMENTS
lookup_value: The value that you intend to search for and return the column number.
row_ref: The row from which to lookup the specific value.

ADDITIONAL NOTES
Note 1: Given the formula uses the Excel MATCH function, the specific value is case-sensitive.
Note 2: If the formula cannot locate the specified value from the selected range it will return an #NA error.