google-sheetsgoogle-sheets-formulaspreadsheet

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 :

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))),"-")))))
    

    enter image description here