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:

Next title|
3|Next title

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


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


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

          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) ), 

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.

  • Thanks @doubleunary I didn't expect going this way and I have to admit I still don't really understand how this formula works. If it's possible to detail the steps/process in your answer, this would be of great help for similar situation I (and others) might face in the future
    – Exdefoot
    Oct 27 at 6:47
  • Edited the answer to include a short explanation. Oct 27 at 8:37
  • Perfect, thanks. I'll try to replicate the idea on other data
    – Exdefoot
    Oct 27 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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