Need help to map bulk list of urls from another list in excel using vlookup or similar

[mohtashimkhan]

Hi everyone,

I am facing an issue to map the urls from one list to another. One list has around 1000 urls in different format (magento based) and the other list contains urls (1000) in shopify format. I couldn't find any primary key to map all these urls accordingly.

So, what I am trying to figure out is -

A formula/string/script that can map and vlookup the urls from one sheet to another if I add multiple text (as in "gucci", "versace" etc.) and if anyone of them or all of them (text) exist/s in any url in the previous list, they all comeup into the other list against the corresponding url as the vlookup works.

Let me share an example here for better understanding -

Magento based urls-
/shop/mens-cologne/paco-rabanne-1-million_192712.1.html
/shop/mens-cologne/paco-rabanne-invictus_201207.1.html
/shop/mens-cologne/paco-rabanne-black-xs_1002484.1.html
/shop/womens-perfume/paco-rabanne-olympea_202615.1.html

Shopify based urls -
/products/1-million-set-for-men-by-paco-rabanne
/products/invictus-intense-eau-de-toilette-spray-for-men-by-paco-rabanne
/products/copy-of-xs-black-eau-de-toilette-spray-for-men-by-paco-rabanne
/products/lady-million-lucky-by-paco-rabanne-for-women-eau-de-parfum-spray
/products/olympea-aqua-eau-de-toilette-spray-for-women-by-paco-rabanne

So I need to create a formula using vlookup (or any other which can work) to match multiple text ("paco-rabanne", "million", "men") in the list of shopify based urls and run that against Magento based urls and then import the best matched urls in front of the shopify based url respectively.

 

[sandy666]

maybe try with Fuzzy Lookup add-in (from Microsoft)