Here is one approach, which relies on using some character (backtick `
in my example) that is not contained in any header. I don't think I ever put backticks in any spreadsheets of mine but if you did, pick another character.
D2 =REGEXEXTRACT(JOIN("`", FILTER(E$1:$1, LEN(E2:2))), "[^`]*$")
(and drag down the column D). Explanation:
FILTER
selects the 1st row entries from the columns in which the 2nd row is nonempty (LEN>0
)
JOIN
joins these entries, separating them by backticks
REGEXEXTRACT
extracts the part of string matching [^`]*$
, which is a regular expression saying: the longest substring at the end that does not contain backticks. This will be the header of the last column with nonempty 2nd row.