Need formula to find partial string match in a separate column and return adjacent value.

Here is the formula I've been working w/ so far:

Please Login or Register  to view this content.

My formula should be using the cells in column H (second sheet) to find partial matches in column E (first sheet) and then returning the value of the cell in the same row from column V (first sheet).

For context, I'm working with product image file names, product SKUs, and product titles/names. The filenames and SKUs will partially match b/c the filenames will contain the product SKUs near the beginning of the filename. My goal is to partially match the filename and SKU in order to return the product title/name.

Instance:

(H2, second sheet) filename 1sirenred-product-2-ambassador-mary partially matches with SKU sirenred (E2, first sheet) and return value "Siren" in Red (cV2, first sheet - Its a kind of coincidence that all the cells were in the same rows in ea sheet. Its just where I happened to start when I began testing).

Expected results (cells in second sheet which are not returning the correct values):

I12 = Metal Square Flat Top Shields in Sunset
I24 = Oversized Giatta in Haze
I29 = Square Flat Top Shields in Black-Out

Workbook has 2 sheets. In each sheet, the columns involved are highlighted in blue. The cells which aren't showing the expected results have been highlighted in black.

Additionally, its problematic that I can't include any unoccupied rows within the formula b/c as time goes on, I'd like to copy additional filenames, SKUs, and products as we add more items to our catalog. Its not critical but having to adjust to formula for new rows definitely slows down the process (I was advised that I should not have unoccupied rows in the formula).

Your description and your file do not match. The multiple occurrences of the same words all over makes this very confusing. What do you expect to see? Where do you expect to see it? Please read the yellow banner at the top of the page and add some manually calculate dresults.

For the sample and expected outputs provided try modifying the array entered formula to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.