Match one cell with partial contents of another

[craigbrown70]

Hi there,

I have a list of product numbers, for example one might be '123456' and I want to match this to an unordered list of URLs which contain the product numbers for example exampleurl.co.uk/folder/products/123456_123_example.jpg

So I want to match the product number with its corresponding URL however the URLs are different lengths so delimiting to pull back the product number didn't work as I have over 17,000 URLs.

Any recommendations would be a huge help!

Thanks
Craig

[BadlySpelledBuoy]

You could use a wildcard in your lookup formula (VLOOKUP or INDEX/MATCH for example).

Unless you have exampleurl.co.uk/folder/products/123456_123_example.jpg and then exampleurl.co.uk/folder/products/1234567_123_example.jpg it could work.

Maybe posting a sample workbook that replicates your data layout would be useful for providing a solution.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

BSB

[craigbrown70]

Thanks BSB - I've just uploaded an example. In tab one is the list of product numbers I need to match the URL to and in tab 2 is a list of URLs which are different lengths and wouldn't be order.

I've only done a traditional VLOOKUP before i.e. use column A to find the chosen data in sheet 2 for for the corresponding product number in column A - hence the reason I'm stuck as here I only have one list of product numbers and one list of URLs.

Do you know what the formula I'd need to use for the wildcard?

[BadlySpelledBuoy]

Try either of the below formulas in B2 on Product Numbers and copy down:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The *'s are the wildcards.

You may need to swap commas for semi-colons on a Mac.

BSB

[craigbrown70]

You are a life saver thank you so much