Arrayformula to get closest non-numeric value above

Question

I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example:

Title|
1|Title
2|Title
Next title|
3|Next title
etc.

I was able to do it with a formula per row (here in B5 for instance):

=IF(ISNUMBER($A5),INDEX($A:$A,MAX(NOT(ISNUMBER($A$1:$A5))*(ROW($A$1:$A5))),1),"")

But as I have thousands of lines I'm looking for an =ARRAYFORMULA() solution, if possible.

Example to play with: https://docs.google.com/spreadsheets/d/1VQpXs8-yPcLwLTxqWJnHFW-xlOsdmwxQHgxbQpDgU1k/edit?usp=sharing

Answer 1

One way to do that is to use vlookup() with is_sorted set to true, like this:

=arrayformula( 
  iferror( 
    proper( 
      regexextract( 
        vlookup( 
          if( isnumber(A2:A), text(row(A2:A), "000000") & "µ" & A2:A, iferror(1/0) ), 
          if( istext(A2:A), text(row(A2:A), "000000") & "µ" & A2:A, iferror(1/0) ), 
          1, 
          true 
        ), 
        "µ(.+)" 
      ) 
    ) 
  ) 
)

The true will make vlookup() find the first non-blank value that is equal to or less than the key, provided that the key column in the data range is sorted. The if() bits make sure that search keys only appear in rows where column A contains a number, and that the data range only contains suitable keys in rows where column A contains text.

See the new Solution sheet in your sample spreadsheet.