[SOLVED] Using Lookup formula in URLs to match broad matches

Using Lookup formula in URLs to match broad matches

I'm fetching a list of URLs from my sitemap on a weekly basis. Ideally, I would like to categorise each page into a category.

The problem is I have two kind of pages :

Broad match pages (everything that contains /blog/ will be in the "blog category").
Exact match pages (that sometimes contains the URL include in the broad match pages).

To cover this, I included a table of categorisation that matches the REGEXMATCH version of a URL with its category.

Basically, I'm searching for a formula that will lookup the category from the table of categorisation in REGEXMATCH and rendered the category in my list of URLS.

Here is a sample spreadsheet to better understand my issue. I'm open to any idea if you think using regexmatch is not the best plan to achieve the URL categorisation.

Thanks you in advance for your help

Solution

Here's one approach you may test out:

=map(B3:B,lambda(Σ,if(Σ="",,let(Λ,'Table of Categorisation'!B3:C20,Ξ,sort(Λ,right(index(Λ,,2))="$",,len(index(Λ,,2)),),
 ifna(+filter(index(Ξ,,1),regexmatch(Σ,index(Ξ,,2))),"-")))))