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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]URL
[/TD]
[TD]Product name
[/TD]
[/TR]
[TR]
[TD]crzyurl.com/cms/type/core/s/865278/res/600x1024/text/uncompressed/ver/4.0.2/lang/en/fname/assets.zip
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]crzyurl.com/cms/type/core/pId/5/res/320x480/text/pvr/ver/1.0.39/lang/en/fname/320480_pvr.zip
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

On a seperate work sheet I have the product code mapping (hundreds of them):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID
[/TD]
[TD]Product name
[/TD]
[/TR]
[TR]
[TD]865278[/TD]
[TD]Product 1
[/TD]
[/TR]
[TR]
[TD]320480
[/TD]
[TD]Product 2
[/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH="bgcolor: #4F6076"]crzyurl.com/cms/type/core/s/865278/res/600x1024/text/uncompressed/ver/4.0.2/lang/en/fname/assets.zip[/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"]Product 1[/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"][/TH]
[TH="bgcolor: #4F6076"]Product ID[/TH]
[TH="bgcolor: #4F6076"]Product name[/TH]
[/TR]
[TR]
[TD]crzyurl.com/cms/type/core/pId/5/res/320x480/text/pvr/ver/1.0.39/lang/en/fname/320480_pvr.zip[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]865278[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]320480[/TD]
[TD]Product 2[/TD]
[/TR]
</tbody>[/TABLE]

 

[DiLaflamme]

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


Example:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]URL[/TD]
[TD]Product name[/TD]
[/TR]
[TR]
[TD]crzyurl.com/cms/type/core/s/865278/res/600x1024/text/uncompressed/ver/4.0.2/lang/en/fname/assets.zip
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]crzyurl.com/cms/type/core/pId/5/res/320x480/text/pvr/ver/1.0.39/lang/en/fname/320480_pvr.zip
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On a seperate work sheet I have the product code mapping (hundreds of them):
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Product ID
[/TD]
[TD]Product name
[/TD]
[/TR]
[TR]
[TD]865278
[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD]320480
[/TD]
[TD]Product 2[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

did the formula solution work for you?

 

[DiLaflamme]

YIPEE! Yes! Thank you VBA Geek!