Return column header for last date in Google Sheets as an array formula

Question

I've got a number of columns used to track some stages of Google Sheets entries with dates. I'd like a column that returns the latest stage for each entry, preferably using an array formula since this is a list that will be constantly added-to and copying the formulae down to new rows is a pain (this is something that will end up in with an end-user so needs to be straightforward).

This is a sample of my desired input/output.

I tried using array formulae combined with this lookup trick, but I think changing the inputs of that formula to multi-row ranges means the result ignores what row the entry is on. For instance wrapping the lookup in ARRAYFORMULA and applying it to the sample returns "Stage4" on every line. Alternatively I think there might be a way to do this using QUERY/FILTER but I'm getting nowhere. Thanks everyone.

EDIT: For clarification, the 'Stages' are not going to be numbered, but will be text fields (potentially dynamic ones, hence my reticence to use an HLOOKUP).

What is the logic to the output's ordering? Why not just use UNIQUE(TRANSPOSE(<row of stages>)) ? — ttarchala
The output is the latest stage that has a date in the corresponding columns on the left. It's just coincidence that each stage is represented once. If the cell under the 'Stage3' header had a date, then the first of the outputs would be 'Stage3' not Stage2 — neophlegm

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2018-09-08T01:04:05

Assuming "Stage1" is in cell A2 and that you can not jump stages. You can use this formula on F3 to auto-populate your output:

=ArrayFormula(transpose(
    split(
      concatenate(
        if((A3:D<>"")*(B3:E=""),A2:D2,"")&
        if(column(A2:D2)=column(D2),char(9),"")
      )
      ,char(9),true,false
    )
 ))

You can see that we're determining the stage by checking the not empty that is followed immediately by an empty one. Hence the no jumping stages rule :-) Also, this requires an extra empty column after the last stage column (in your example, column E must be empty).

ps: added bonus, this formula also works for "no stage" rows ;-)

Return column header for last date in Google Sheets as an array formula

1 Answers