How to automatically apply a hyperlink formula with relative reference to a whole column?

Question

I have the following formula in every cell in my C column:

=IF(ISBLANK(B4),"",CONCATENATE("https://www.url.com/",B4))

If the cell in the B column has content, it takes that content and concatenates it onto the end of a URL. It’s very useful, but the formula does not copy to new rows when new rows are created, requiring me to copy the formula to the new cells manually.

I’ve tried to use ArrayFormula and Indirect Address to automatically apply this formula to every cell in the C column (C3:C) but all I could get it to do was concatenate every cell in the B column onto the end of the URL.

How can I write a formula that automatically applies to every column from C3:C which concatenates the content from only the cell immediately to the left?

Answer 1

You do not actually even need the CONCATENATE function.

The simplest way to do this is to apply in cell C3 the following formula:

=ARRAYFORMULA(IF(B3:B<>"","https://www.url.com/" &B3:B,""))

Functions used:

ARRAYFORMULA