Forum Discussion
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.
https://archiplexusdxb-my.sharepoint.com/:x:/g/personal/salhashimi_archiplexus_com/EbHZqJr_KKNDk9B23Vw00LUBDOHiY2XiAii8ug45pnSafg?e=dczZay
Thank you in advance!
=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.
5 Replies
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")=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.
- Thank you so much for this! This is exactly what I was looking for 🙂
See the attached version. Should work in Excel in Office 2021, Microsoft 365 and in Excel Online.
