Returning part of column header corresponding to matched value in multiple spreadsheets

Patrick2788 

It is never enough to leave well alone!  I have gone for a more verbose formula in which every step is assigned a name in much the same manner as one could us helper ranges in traditional spreadsheets.

= LET(
    headerValue,  VALUE(TEXTAFTER(header," ")),
    stack,        VSTACK(tables),
    stackedTable, FILTER(stack, TAKE(stack, , 1) <> ""),
    stackRowHdr,  TAKE(stackedTable,,1),
    stackValues,  ReduceTableλ(stackedTable,headerValue),
    XLOOKUP(nameList, stackRowHdr, stackValues,0)
  )

The first line extracts the final value from each header

The next two lines stack the 3D range and removes empty rows

The table is then reduced to two columns, the first being the row headers and the second contains the values corresponding to the first 1 of each row (using a Lambda function).

These two columns form the lookup array and return array for the final lookup. 

ReduceTableλ 
//"Looks up the first 1 in each row and returns the header"
= LAMBDA(table,header, 
     BYROW(table, 
        LAMBDA(row, XLOOKUP(1,row,header,0))
     )
  );

As you see, this is the antithesis of the 'rule of thumb' that says no formula should be longer than your thumb.  The idea is more one of 'if the formula is not as long as your arm, you haven't explained the steps of your calculation sufficiently'.