Excel: Find value across multiple columns and return header column

Question

I am trying to find a way to look for "Unknown" value across multiple columns. When it is present, I would like to return all of the header columns that the value appears in into one cell for each row in column B.

For example for row 2 (the first row below my header row), I want it to return every column name that "Unknown" appears from Column F to Column Y for row 2 only and put the column name in B2. I want to repeat that process for all of my 9064 rows.

I am using Excel 2010. I looked up Match Index but was not able to find a way to do what I wanted to do. Is there a way to look for a value across multiple columns and return every column header that values appears in for that row and put all of the column headers into one cell?

Data View

" I want the number of times "Unknown" appears" and " return every column header that values appears in for that row and put all of the column headers into one cell" seems to contradict... Can you give an explicit example of the result? — EBH
I've made a correction, that was worded poorly. I would like to return every column name that Unknown is present in for each row. So in my image "Data View", for example, I would like it to have B2 say Install_Method_CD Manuf_Cd Coating_Cd Wall_Thickness Join_Method_Cd etc. because Unknown is in those columns. — elagarde

Domenic Domenic · Accepted Answer · 2016-07-21T23:51:38

Here's a custom function. First place the following code in a regular module (Alt+F11 >> Insert >> Module >> Copy/paste >> Alt+Q)...

Function AConcat(a As Variant, Optional Sep As String = "") As String
' Harlan Grove, Mar 2002

    Dim Y As Variant

    If TypeOf a Is Range Then
        For Each Y In a.Cells
            AConcat = AConcat & Y.Value & Sep
        Next Y
    ElseIf IsArray(a) Then
        For Each Y In a
            AConcat = AConcat & Y & Sep
        Next Y
    Else
        AConcat = AConcat & a & Sep
    End If

    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))

End Function

Then enter the following formula in AA2, confirm with CONTROL+SHIFT+ENTER, and copy down:

=SUBSTITUTE(AConcat(IF(LEFT(F2:Y2,3)="Unk",", "&$F$1:$Y$1,"")),", ","",1)

Excel: Find value across multiple columns and return header column

1 Answers