Explanation
This is a standard INDEX formula. The only trick to the formula is the use of a structured reference to return a range for the table headers:
Table1[#Headers]
This range goes into INDEX for the array argument, with the index value supplied from column H:
=INDEX(Table1[#Headers],H5)
The result is the name of the first item in the header, which is “ID”.
Although the headers are in a horizontal array, with values in columns, INDEX will use the row number as a generic INDEX for one-dimensional arrays like this and correctly return the value at that position.
Explanation
This formula sits inside a small summary table with percentile values in column F and gender values in G4 and H4. Working from the inside out, the IF function is set up like this:
IF(Table[Gender]=G$4,Table[Score])
Here, each value in the gender column is tested against the value in G4, “Male”.
The result is an array of boolean values like this:
{88;85;77;FALSE;FALSE;FALSE;83;FALSE;FALSE;79}
Only scores associated with males make it into the array, female scores are translated to FALSE. This array goes into the PERCENTILE function with the k value from F5, 90%.
PERCENTILE automatically ignores FALSE values and returns a result of 86.8.
The reference to Gender in G$4 is locked to prevent the row from changing. The reference to k values, $F5 is locked to prevent the column from changing. As a result, the formula can be copied across the range G5:H7.
