search for multiple text strings then return assigned value

[DiLaflamme]

Hello everyone. I am new to this, please bear with me. I hope I am phrasing my question well. Thanks for your help in advance!

I have a spreadsheet with hundreds of URLs, and buried somewhere in that URL is a product code. Convention is not the strong suit here, so the code can be anywhere in the URL string. I'm hoping to search for all of the project codes through the URLs, and if found, insert the product name.

Example:

URL	Product name
crzyurl.com/cms/type/core/s/865278/res/600x1024/text/uncompressed/ver/4.0.2/lang/en/fname/assets.zip
crzyurl.com/cms/type/core/pId/5/res/320x480/text/pvr/ver/1.0.39/lang/en/fname/320480_pvr.zip

<tbody>
</tbody>

On a seperate work sheet I have the product code mapping (hundreds of them):

Product ID	Product name
865278	Product 1
320480	Product 2

<tbody>
</tbody>

I would like to search for all of the product IDs in the URLS. When it finds a match, insert the Product Name in the cell next to the URL.

Please tell me this is possible!

 

[VBA Geek]

what are the criteria to identify the product code?

 

[VBA Geek]

try this

the two urls in A1 and A2, your product code-product name table in N1:O3

try in I1:

=LOOKUP(2,1/ISNUMBER(SEARCH($N$2:$N$3,A1)),$O$2:$O$3)

crzyurl.com/cms/type/core/s/865278/res/600x1024/text/uncompressed/ver/4.0.2/lang/en/fname/assets.zip								Product 1					Product ID	Product name
crzyurl.com/cms/type/core/pId/5/res/320x480/text/pvr/ver/1.0.39/lang/en/fname/320480_pvr.zip								Product 2					865278	Product 1
													320480	Product 2

<tbody>
</tbody>

 

[DiLaflamme]

Wow, thanks so much for a quick response!
No those values are not a match. The matches are:


Example:

URL	Product name
crzyurl.com/cms/type/core/s/865278/res/600x1024/text/uncompressed/ver/4.0.2/lang/en/fname/assets.zip
crzyurl.com/cms/type/core/pId/5/res/320x480/text/pvr/ver/1.0.39/lang/en/fname/320480_pvr.zip

<tbody>
</tbody>

On a seperate work sheet I have the product code mapping (hundreds of them):

Product ID	Product name
865278	Product 1
320480	Product 2

<tbody>
</tbody>

 

[VBA Geek]

did the formula solution work for you?

 

[DiLaflamme]

YIPEE! Yes! Thank you VBA Geek!