How do I find the heading above the first match to the right with a checkmark?-CodePudding

I have a

update:

=ARRAYFORMULA(TEXT(TRANSPOSE(ARRAY_CONSTRAIN(SORTN(SORT(TRANSPOSE(
 FILTER({HLOOKUP(COLUMN(G1:AK1), IF(G1:AK1<>"", {COLUMN(G1:AK1); G1:AK1}), 2, 1); 
 H2:AL2}, G2:AK2=TRUE)), 2, 1, 1, ), 9^9, 2, 2, 1), 9^9, 1)), "00"))

CodePudding user response：

Use filter(), like this:

= filter($G1:$AK1, $G2:$AK2, $H2:$AL2 = A1)

See the new Solution tab in your sample spreadsheet.

The formula requires that you repeat the revision number. You can do that by using a formula to copy the value to the left as required. If the duplicated revision numbers get in the way, hide them by setting the text color to white, or put the duplicates in a row of their own, and hide the row.