Google Sheets - Arrayformula with textjoin, and, if

Question

I'm trying to figure out how to make my arrayformula with textjoin to work, but the formula does not copy to another row.

I want the job no automatically generate when i write data in F3 cell & G3 cells. But i having problem the formula does not automatically inserted to I3.

My formula looks as following right now:

=ArrayFormula(IF(AND(ISBLANK(F2),ISBLANK(G2))," ",TEXTJOIN("-",TRUE,LEFT(F2,4)&ROW()-1,RIGHT(G2,2))))

Any Google Sheets pro's out there who could help me out in a direction?

Would be thankful for any help!

Answer 1

Both textjoin() and and() are aggregating functions so they do not work the way you seem to expect in an array formula context.

To make it work, use the concatenation operator & and boolean multiplication, like this:

=arrayformula( 
  if( 
    len(F2:F) * len(G2:G), 
    left(F2:F, 4) & sequence(rows(F2:F)) & "-" & right(G2:G, 2), 
    iferror(1/0) 
  ) 
)

To evaluate an array formula over a range, you need to use multi-cell range references instead of single-cell references. The formula above uses open-ended range references like F2:F to evaluate all the way to the bottom of the sheet.

Since the formula fills all of the column from row 2 down, you must place it in row 2 of a column where there are no values or formulas from row 2 down.