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!

Improve this question
1

1 Answer 1

Reset to default
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.

Improve this answer
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 Commented Dec 15, 2021 at 7:46

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.