My research has failed me (or perhaps I’m not wording my question properly) on Google and these forums. What I’m trying to do is find all occurrences of -1 from the table in the pic and list out the corresponding values from column A and row 1.
I am currently able to list the values from column A using:
=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$G$8=-1,ROW($B$2:$G$8)-1),ROW(1:1))),"")
It returns:
But, I’ve not found any formula that accurately retrieves the value from row 1.
The goal is to have this:
>Solution :
Just add that to the condition and use COLUMN() for the return and COUNTIF for k.
=IFERROR(INDEX($B$1:$G$1,SMALL(IF(($B$2:$G$8=-1)*($A$2:$A$8=J2),COLUMN($B$2:$G$8)-1),COUNTIF($J$2:J2,J2))),"")
