Google-sheets – Grab text of first row of column with last data value

google sheets

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.

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.

If you're looking for just the GDP of France then this will do:

=VLOOKUP("France",data!A2:C,2,FALSE)

Assuming the data starts in row 1, try in B1:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),))

Out of interest, the formula simplifies a bit if you are retrieving from cells below:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))