Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have the following string in column H and I.


I want to have a auto-generated J column, which results like:


So I put the following formula in J1:

= ARRAYFORMULA (IF(ROW(J:J)=1;"formulaTest";CONCATENATE(H:H1, I:I1 ) ))

But the result I got is:


What is going wrong with my formula?

share|improve this question

migrated from stackoverflow.com Jan 17 at 13:20

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 1 down vote accepted

This is what you are looking for:

=ARRAYFORMULA((IF(ROW(J:J)=1,"formulaTest",H1:H & I1:I)))
share|improve this answer
Great! It finally works now!. Though I still don't know why I can not use "CONCATENATE", instead I need to use "&" in this formula. –  srjohnhuang Jan 17 at 6:53
@srjohnhuang With the CONCATNATE, the complete column range is fed to the ARRAYFORMULA. Using the ampersand, only the single entries are fed, giving you the desired result. –  Jacob Jan Tuinstra Jan 19 at 12:01
The reason you can use & but not concatenate() is that concatenate can take a range whereas & can only join specific discreet values. –  Matthew Quinlan Jun 30 at 18:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.