2

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.

3 Answers 3

2

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

enter image description here

1

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.
1
  • Instead of backticks, one might want to use some high-code Unicode character like I did here.
    – user79865
    Jul 16, 2015 at 23:17
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.