I need to return multiple column headers from a ta

Question

I have a table in Excel, that is set up as follows;

What I need to be able to do, is on another sheet, based on the value of a drop down box where the person's name is selected (i.e. Test 1, Test 2, etc...), perform a lookup against the table and in the cell next to the drop down return the headers where the value in the Cell is Y.

For example, based on the table above, If Test 1 was selected from the drop down then the value returned should be

Skill 1, Skill 4

Any advice on this would be much appreciated. I have tried to follow the instructions in the answer on this post but have been unsuccessful.

Answer 1

Try the following UDF():

Public Function GetHeaders(r1 As Range, r2 As Range) As String
   Dim r As Range, s As String, rr As Range, rTOP As Range
   GetHeaders = ""
   s = r1.Text
   Set rTOP = r2.Rows(1).Cells

   For Each r In r2.Columns(1).Cells
      If r.Value = s Then
         For Each rr In Intersect(r.EntireRow, r2).Cells
            If rr.Value = "Y" Then
               GetHeaders = GetHeaders & "," & Intersect(rr.EntireColumn, rTOP).Value
            End If
         Next rr
      End If
   Next r
   GetHeaders = Mid(GetHeaders, 2)
End Function

So with the data in Sheet1, put the pull-down in Sheet2 cell B1 and in Sheet2 cell A1 enter:

=GetHeaders(B1,Sheet1!A1:F5)

NOTICE: we pass the entire table, including header rows / columns.