Difficulties using ArrayFormula

Question

I am trying to use ArrayFormula to populate row data. I have the following formula that work perfect when I drag down, however I am unable to figure out how I should adapt it to work with ArrayFormula.

Equation to populate data in each row where I changed U2 to U2:U for use with ArrayFormula:

=IF(HOME!U2:U<>"",TRANSPOSE(IMPORTXML(LEFT(HOME!U2:U,FIND("#",SUBSTITUTE(HOME!U2:U,"/","#",4))-1),"//strong[@data-e2e='video-views']")),"")

And this is the corresponding error when I use it with ArrayFormula ("Function FIND parameter 2 value should be non-empty. "):

Appreciate any help I can get with this, thanks!

Answer 1

IMPORTXML is not supported under ARRAYFORMULA. you will need to stick to a per-row solution.

Answer 2

The FIND formula in Google Sheets (you can read more about it here), needs a non-empty text_to_search parameter. In your case this parameter is obtained by using the SUBSTITUTE formula (you can read more about it here), which will return an empty value if it is unable to perform the substitution.

This can be solved by slightly modifying the IF statement at the beginning, with the objective to only return the FIND function if there is no empty value/error in the process. In the following example, I am doing it with the IFERROR function (you can read more about it here).

==ArrayFormula(IF(IFERROR(FIND("#",SUBSTITUTE(A1:A,"/","#")))<>"",FIND("#",SUBSTITUTE(A1:A,"/","#")),"empty"))

with the following result: