Formula to search table and return multiple column

问题:

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)))}

回答1:

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