Return column name of a specific value

To return a column name of a specific value we need to apply a combination of Excel SUBSTITUTE, ADDRESS and MATCH functions

Example: Return column name of a specific value

Return column name of a specific value

METHOD 1. Return column name of a specific value

EXCEL

=SUBSTITUTE(ADDRESS(1,MATCH(C6,4:4,0),4),1,"")
The formula uses the Excel SUBSTITUTE, ADDRESS and MATCH functions to return the column name for a specific value ("Cereal") that is captured in row 4. The formula will only return the column name of the first occurrence of the specific value.

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

VBA

Sub Return_column_name_of_a_specific_value()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to return the column name of a specific value held in row 4
ws.Range("C7") = Application.WorksheetFunction.Substitute(ws.Cells(1, Application.WorksheetFunction.Match(ws.Range("C6"), ws.Range("4:4"), 0)).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")

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 name for.

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

EXPLANATION

EXPLANATION
To return a column name of a specific value we need to apply a combination of Excel SUBSTITUTE, ADDRESS and MATCH functions.
FORMULAS
=SUBSTITUTE(ADDRESS(1,MATCH(lookup_value,row_ref,0),4),1,"")
ARGUMENTS
lookup_value: The value that you intend to search for and return the column name.
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.