Forum Discussion

shwan_dxb's avatar
shwan_dxb
Copper Contributor
Sep 10, 2022
Solved

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 se...
excel
Excel for web
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Sep 10, 2022

    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.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 11, 2022

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

Resources