Google Sheets – Does ArrayFormula Work with Concatenate?

google sheetsgoogle-sheets-arrayformula

I am trying to make an ArrayFormula that see if column D is empty or not

If it is not empty it will then take a number and concatenate to generate an url to show an img from a URL.

My current formula (that doesn't work)

=ARRAYFORMULA(if(ISBLANK(D6:D12),"wosh",ARRAYFORMULA(IMAGE(CONCATENATE("http://website.com/SB_Draft_",D6:D12,".jpg")))))

If I change the D6:D12 into just D6 it shows the image, but it is the same image for all the array, otherwise it doesn't grabs the numbers from col D

What am I doing wrong?

Best Answer

Let's drop the "image" part for the purpose of debugging, and look at text concatenation. The formula

CONCATENATE("http://website.com/SB_Draft_", D6:D12, ".jpg")

concatenates: http string, then everything in D6:D12, then "jpg". This is because it has internal logic for processing arrays: you can give in an array and it will concatenate all of it into one string. Which is useful, but not what you want here.

Instead, use the binary concatenation operator &. For example, 

=ARRAYFORMULA("http://website.com/SB_Draft_" & D6:D12 & ".jpg")

will return an array of URLs, one for each entry in D6:D12. The full formula would be

=ARRAYFORMULA(if(ISBLANK(D6:D12),"wosh", IMAGE("http://website.com/SB_Draft_" & D6:D12 & ".jpg")))
Related Topic