Search through columns and find specific text and replace that text with header column

Another:

Sub adamzee()
Dim rcell As Range
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For Each rcell In Sheets("Data").UsedRange.offset(1)
    If rcell.Value = "x" Then
        Cells(rcell.Row, "A").Copy Sheets("Output").Range("A" & Rows.count).End(3)(2)
        Cells(rcell.Row, "B").Copy Sheets("Output").Range("B" & Rows.count).End(3)(2)
        Cells(rcell.Row, "C").Copy Sheets("Output").Range("C" & Rows.count).End(3)(2)
        Cells(1, rcell.Column).Copy Sheets("Output").Range("D" & Rows.count).End(3)(2)
    End If
Next rcell
Sheets("Output").Cells(1, 1) = "State"
Sheets("Output").Cells(1, 2) = "Store name"
Sheets("Output").Cells(1, 3) = "Store number"
Sheets("Output").Cells(1, 4) = "SKU"
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

PS: Created a Sheets Output to memic the results sheet.