SOLVED

New Contributor

# Extracting relationships from a heat map

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

5 Replies

# Re: Extracting relationships from a heat map

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

best response confirmed by shwan_dxb (New Contributor)
Solution

# Re: Extracting relationships from a heat map

``=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.

# Re: Extracting relationships from a heat map

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

# Re: Extracting relationships from a heat map

@shwan_dxb For O365 can utilize FILTER().

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

# Re: Extracting relationships from a heat map

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")``````