Grab text of first row of column with last data value

Question

Essentially, I want the values in column D to be the value of the header row of the column with the last data value.

For example, if H27 is the last cell with data in row 27, I want D27 to be the text value of H1.

There will be blank values in each row.

Answer 1

Add the following formula in D2.

Formula

=ARRAYFORMULA(IF(ROW(D2:D) = QUERY({H2:H, ROW(H2:H)},"SELECT Col2 WHERE Col1 <> '' ORDER BY Col2 DESC LIMIT 1"), H1, ""))

It will automatically show the title of the header in column H in the last entry of column H, in column D.

Screenshot

Answer 2

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:

1. FILTER selects the 1st row entries from the columns in which the 2nd row is nonempty (LEN>0)
2. JOIN joins these entries, separating them by backticks
3. 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.

Answer 3

Shorter (in D1 and copied down to suit):

=if(row()=match("zzzz",H:H),H$1,"")

IF
ROW
MATCH

Relies on zzzz not being found in ColumnH - if it is, use more zs.