Sep 10 2022 02:24 PM
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.
Thank you in advance!
Sep 10 2022 02:53 PM
See the attached version. Should work in Excel in Office 2021, Microsoft 365 and in Excel Online.
Sep 10 2022 03:01 PM
Solution=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.
Sep 11 2022 12:54 AM
Sep 11 2022 01:25 AM
@shwan_dxb For O365 can utilize FILTER().
=TRANSPOSE(FILTER(Tabelle1!C1:AF2,FILTER(Tabelle1!C4:AF33,Tabelle1!B4:B33=B4)=3))
Sep 11 2022 02:15 AM
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")
Sep 10 2022 03:01 PM
Solution=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.