Formula to search table and return multiple column headers

Question

I am trying to enter a certain value in a cell, search a table for that value (vertically and horizontally), then return the column header of the columns containing that value. My formula works, but it will only return 1 column header, and I need it to return multiple

Formula:

{=INDEX(Sheet2!A1:J31,,MAX(IF(Sheet2!A1:J31=B2,COLUMN(Sheet2!A1:J1)-COLUMN(Sheet2!A1)+1)))}

Without testing your formula for actual errors, I can see that it is an array formula, and it looks like you are only putting it in a single cell. When you have an array formula it returns an array of results, and if you don't do anything with that array (like, pick the MAX number, SUM all the numbers, pick a value with the INDEX function, etc.), it will return the first item in the list (usually). You will need some way to display multiple values. With String results in an Array, there is no simple way to do this in a single cell. How do you want your results displayed? — Grade 'Eh' Bacon
Thank you! Could I either receive the results in different cells, or delineated in one cell? After the results are given, I'm going to compare them to another set of results ([results from 4 values entered] vs [results from 4 another values entered]). I'm looking for matches/mismatches between these 2 sets of data. The table array search just seems like it could be simpler, but I haven't found another way yet. Any ideas? — Chris Wheeler

swilliams swilliams · Accepted Answer · 2015-08-06T20:40:08

I'm not aware of a built in Excel formula that will do this, but this can be accomplished with a user-defined function.

Here is an example dataset, showing the results and the formulas used:

Here is the code for the user-defined functions:

Function SearchColumns(SearchValue As String, SearchRange As Range,    Delimiter As String) As String

SearchColumns = ""

Dim counter As Integer
For i = 1 To SearchRange.Columns.Count
    counter = 0
    For j = 1 To SearchRange.Rows.Count
        If SearchRange.Cells(j, i).Value = SearchValue Then
            counter = counter + 1
        End If
    Next j
    If counter > 0 Then
        SearchColumns = SearchColumns + SearchRange.Cells(1, i).Value + Delimiter + " "
    End If
Next i

SearchColumns = Left(SearchColumns, Len(SearchColumns) - 2)

End Function

Function SearchRows(SearchValue As String, SearchRange As Range, Delimiter As String) As String

SearchRows = ""

Dim counter As Integer
For i = 1 To SearchRange.Rows.Count
    counter = 0
    For j = 1 To SearchRange.Columns.Count
        If SearchRange.Cells(i, j).Value = SearchValue Then
            counter = counter + 1
        End If
    Next j
    If counter > 0 Then
        SearchRows = SearchRows + SearchRange.Cells(i, 1).Value + Delimiter + " "
        End If
    Next i

SearchRows = Left(SearchRows, Len(SearchRows) - 2)

End Function

Formula to search table and return multiple column headers

1 Answers