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)


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?

1 Answer 1


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")))
  • Thank you, it worked perfect, I didn't know things could be wrong with concatenate and array, but this solution is simplier! Thnak you!! Mar 12, 2018 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.