Google-sheets – Return different value for condition in ARRAYFORMULA

formulasgoogle sheetsgoogle-sheets-arrayformula

I have an ARRAYFORMULA that combines text with a number.

A B
1 textA 0
2 textB 1
3 textC 2 
=ARRAYFORMULA(A1:A3&" "&B1:B3)

But if the value in the B column is 0, I want the output cell to be empty like this:

C D E
1 textB 1 textC 2

What would be the best way to do this?

I have tried adding an if statement before the arrayformula that looked something like this

=IF(B1:B3=0)," ",ARRAYFORMULA(A1:A3&" "&B1:B3)

I assume that this is checking if the entire range equals 0, but I don't know if there is a way to check values per cell in the range.

I did not realize that ARRAYFORMULA would return a vertical list with a vertical range (although I probably should have figured that).

Best Answer

Neither formula shown in your post would result in what you show in your second image. Your second formula seems it would have worked to produce a vertical result as desired if you had wrapped it in ArrayFormula( ) as you did with the first.

Best I can tell from what you show, you want this in C1:

=ArrayFormula(TRANSPOSE(IF(B1:B3=0,,A1:A3&" "&B1:B3)))

Related Topic