Thread: Advanced - Return Header in X and Y Axis for Specific Value in Table

I have a large data set about 300x300 with a column and row header (first row of X and Y axis serves as header). I have made a sheet where each cell in the 300x300 matrix returns a TRUE or FALSE value. I need a formula that will return the header (X and Y axis) for each cell with value "TRUE". So ideally, if i had rows labeled A, B, C, etc and columns labeled A, B, C, etc, if the cell in (B,B) were TRUE, i could see an output where I get a return (B,B). Much thanks in advance for any help you can provide!

Here is the way I would do it.

Say your table is in A1:F7 with column headers in B1:F1 and row headers in A2:A7... so main data is in B2:F7

in a free row below, say in B8 enter helper formula:

=A8+COUNTIF(B2:B7,TRUE)

and copy across. This cumulatively counts the number of TRUE results in the columns.

Then to get the Column headers, use formula:

=IF(ROWS($A$2:$A2)>$F$8,"",INDEX($B$1:$F$1,MIN(IF(ROWS(A$2:A2)<=$B$8:$F$8,COLUMN($B$2:$F$7)-COLUMN($B$2)+1))))

adjust the ranges (except $A$2:$A2) to suit your data, then confirm the formula with CTRL+SHIFT+ENTER not just ENTER and copy down.

and for the Row headers, assuming the formula above was put in J2:

=IF(ROWS($A$2:$A2)>$F$8,"",INDEX($A$2:$A$7,SMALL(IF($B$2:$F$7=TRUE,IF($B$1:$F$1=J2,ROW($B$2:$F$7)-ROW($B$2)+1)),COUNTIF($J$2:J2,J2))))

also adjust relevant ranges and confirm with CTRL+SHIFT+ENTER not just ENTER and copied down.

You can then make a column that concatenates them, if desired.

See attached