Can you copy down a formula in a column using ArrayFormula that has SUBSTITUTE/LEN/RIGHT functions nested in it?

Question

My Google Sheet has a formula that takes the following URL (as an example)

https://info.example.com/page-one/

and strips it down to the last part. Like this

page-one

It then decides if the keyword in a separate column is used in the naming convention of this page (Yes or No)

What I would like for this formula to do is automatically expand the functions to all the rows that have data. I've been doing this with the ArrayFormula but have not been able to replicate it with these functions.

=IF(ISBLANK(B2),"",IF(LOWER(CONCATENATE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"&","and")," ","-")))=SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1))), "/", ""),"Yes","No"))

Here is a link to the working Google Sheet: https://docs.google.com/spreadsheets/d/1iHkU-rNtNhoOKvW_CWY7WU5OLsMFVqEFNRZlx_R-7RY/edit#gid=1497887942

Answer 1

try:

={"header"; INDEX(IF(B2:B="",,
 IF(REGEXMATCH(A2:A, SUBSTITUTE(B2:B, " ", "-")), "Yes", "No")))}