Look up data in excel dataset and return column headers

Question

In Excel, I have multiple column headers and below each header are items belong to that column header. For instance, it has multiple of server names as the column headers and underneath are the software names listed.

I'd like to build a formula to look up for a name of software and returns the name of the server name or returns multiple server names, since one software name can be installed on multiple servers.

How do I go about doing that with formulas or vbscript in Excel?

Answer 1

Excel 365 Pro Plus with Power Query.
No formulas, no VBA macro.
https://www.mediafire.com/file/aobr14iw8htn3it/04_24_21a.xlsx/file
https://www.mediafire.com/file/bede46sae3kz65x/04_24_21a.pdf/file

Answer 2

This is the exact output results I was looking for.

Can you walk me through how you were able to achieve this using Power Query?

Answer 3

@TB

A variation with dynamic number of columns and names. Assuming data in Excel Table1:

let  
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  
    ChangedTypes = Table.TransformColumnTypes(Source,  
        List.Transform(Table.ColumnNames(Source), each {_, type text})  
    ),  
    UnpivotedAllColumns = Table.UnpivotOtherColumns(ChangedTypes, {}, "Server", "SW"),  
    ConcatServers = Table.Group(UnpivotedAllColumns, {"SW"},  
        {"Servers", each Text.Combine(List.Sort([Server]), ", "), type text}  
    )  
in  
    ConcatServers  

Answer 4

@TB

Herbert's proposal is a good one. A few suggested adjustments:

let  
  Source = ...,  
  Types = ...,  
  
  // Edited the step to replace the default column names (Attribute & Value)  
  UnPivot = Table.UnpivotOtherColumns(Types, {}, "Server", "SW"),  
  Sort = Table.Buffer(  
    // Removed 2nd sorting key (useless)  
    Table.Sort(UnPivot, {{"Server", Order.Ascending}})  
  ),  
  Concat = Table.Group(Sort,{"SW"},  
    // Added ", type text" to the aggregation  
    {"Servers", each Text.Combine([Server],", "), type text}  
  )  
in  
  Concat  

Answer 5

Thank you: HerbertSeidenberg-6295 and Lz-3068 for your help.

I was able to crack and got my list to sort just like you guys showed and scripted.

You guys are awesome. :)

Graciously thank you both of you,

Cheers