Blue Flower

How to Pairing the Row Header and Columns Header if the Value Criterion from the Intersection of the Range is Met

In this Excel tutorial, I will show how we can display the row header and the column header if the condition of the cell ranges is met. In the picture below you can see the situation of our task.

In the 'A1:F6' cell range is our data. In the 'A2:A6' cell range are the names of the workers. Also in the first row in the 'B1:F1' cell range, there are the names of the workers. In the 'B2:F6' cell range, values are displayed in percentages. We want to know which workers (vertically and horizontally) are associated with a certain percentage. So, our requirement is the percentage of the data range intersection and we want to pair the workers with the relevant percentage.

 Display the row of headers and column of headers for the criterion from the intersection

How to pair a Row header and a Column header if a Criterion Matches the Intersection of the Range

This task can be solved in several ways, using different formulas. I have presented three ways to solve the problem. Note that the condition is set in 'I1' cell.

The first example of solving tasks

The 'J1' cell has the formula below. (This formula counts how many times the condition appears in the 'B2:F6' cell range and this cell was used only for the first case to solve the problem)
=COUNTIFS($B$2:$F$6;$I$1)

The ARRAY formula in 'I2' cell is below. (This CSE formula returns the data from the Row Header of the matching condition in the same row, copy the formula below)
=IF(ROWS($I$1:I1)<=$J$1;INDEX($A$2:$A$6;SMALL(IF($B$2:$F$6=$I$1;ROW($A$2:$A$6)-ROW($A$2)+1);ROWS($I$1:I1)));"")

The ARRAY formula in 'J2' cell is below. (This CSE formula returns the data from the Column Header to match the condition in the same column, copy the formula below)
=IF($I2="";"";INDEX($B$1:$F$1;SMALL(IF(INDEX($B$2:$F$6;MATCH($I2;$A$2:$A$6;0);0)=$I$1;COLUMN($B$1:$F$1)-COLUMN($B$1)+1);COUNTIFS($I$2:I2;I2))))

The second example of solving tasks

The ARRAY formula in 'I10' cell is below. (This CSE formula returns the data from the Row Header for the relevant condition in the same row. Note that this CSE formula contains a COUNTIF nested function. Therefore, this formula does not use a 'J1' cell)
=IF(ROWS($I$1:I1)<=COUNTIFS($B$2:$F$6;$I$1);INDEX($A$2:$A$6;SMALL(IF($B$2:$F$6=$I$1;ROW($A$2:$A$6)-ROW($A$2)+1);ROWS($I$1:I1)));"")

The ARRAY formula in the 'J10' cell is below. (This CSE formula returns the data from the Column Header to meet the condition in the same column and is identical in the previous case)
=IF($I10="";"";INDEX($B$1:$F$1;SMALL(IF(INDEX($B$2:$F$6;MATCH($I10;$A$2:$A$6;0);0)=$I$1;COLUMN($B$1:$F$1)-COLUMN($B$1)+1);COUNTIFS($I$10:I10;I10))))

The third example of solving tasks

The ARRAY formula in 'I18' cell is below. (This CSE formula returns the data from the Row Header of the condition is met in the same row. Note that the CSE formula contains a nested COUNTIF function. So, this formula also does not use the data from the 'L1' cell. Also, there is a difference of character '>' and '<=' marked with red font)
=IF(ROWS(A$1:A1)>COUNTIF($B$2:$F$6;$I$1);"";INDEX($A$2:$A$6;SMALL(IF($B$2:$F$6=$I$1;ROW($B$2:$F$6)-ROW($B$2)+1);ROWS(A$1:A1))))

The ARRAY formula in 'J18' cell is below. (This CSE formula returns the data from the Column Header if the condition is met in the same column)
=IF(I18="";"";INDEX($B$1:$F$1;MATCH($I$1;INDEX($B$2:$F$6;MATCH(I18;$A$2:$A$6;0);0);0)))

If you work with a larger range of data and copy the formulas to across, then note that the absolute and relative addresses in the formulas should be correctly set. Of course within the ROWS() function, you can also use other ranges.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.