Extracting relationships from a heat map

[shwan_dxb]

Hi there, I have been trying to figure out a way on how to use multiple formulas to extract information from a heat map. I am trying to identify all the departments that interact with eachother by selecting the number from a drop down list 0,1,2,3. 

What I am trying to do is extract the names of the departments from the rows and respective departments from the columns that satisfy the rating of a specific number within the heatmap. 

For example if  "3" is inputed it will will extract all the department to department names that satisfy this. It would be great to have the associated sector as well. I have the excel file below and on the second sheet is a possible example of the output. 

test.xlsx

Thank you in advance! 

[#2]

@shwan_dxb 

See the attached version. Should work in Excel in Office 2021, Microsoft 365 and in Excel Online.

[#3 · Accepted solution]

@shwan_dxb 

=IFERROR(INDEX(Tabelle1!$C$2:$AF$2,,SMALL(IF(OFFSET(Tabelle1!$C$3:$AF$3,MATCH(Tabelle2!$B$4,Tabelle1!$B$4:$B$33,0),0)=$B$2,COLUMN(Tabelle1!$A:$AD)),ROW(1:1))),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

[#4]

Thank you so much for this! This is exactly what I was looking for :)

[#5]

@shwan_dxb For O365 can utilize FILTER().

=TRANSPOSE(FILTER(Tabelle1!C1:AF2,FILTER(Tabelle1!C4:AF33,Tabelle1!B4:B33=B4)=3))

[#6]

@shwan_dxb 

An interesting feature of your heatmap is that, due to its asymmetry, the result depends upon the direction of the initial lookup.

=   FILTER( rowName,
         XLOOKUP(selectedDept, INDEX(columnName,1,), IF(heatmap<>"x", heatmap, -1)) >= level,
    "none")