0

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

enter image description here

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!

1

1 Answer 1

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.

1
  • Thank you so much, you really help me a lot, I have try the formula and it's work like charm. I'm new to this, so many things need to learn. Thank you for explaining it to me
    – Nabilah
    Dec 15, 2021 at 7:46

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.