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

[majesticoj]

I've found formulas where you can use a "wild card", so to speak, to find a partial string match within another column:

=IFERROR(INDEX(E:E,MATCH("*"&C1&"*",D:D,0)),"")

For instance, this formula would find "dog" (C1) in "took the dog out" (D3) and return "yesterday" (E2).

However my strings in columns C and D aren't laid out in that way.

For instance, I need to partial match and return adjacent value when C1 is "took the dog out" and D3 is "dog". Putting wildcards around C1 wouldn't work.

Example file here.

(Forgive me for not finding a succinct and articulate way to to verbalize the exact formula I'm looking for)

 

[majesticoj]

@DanteAmor Okay. I am having a problem. Perhaps I should have started off with the sheet I would ultimately end up working with...

Would you mind having a look?

Workbook is here.

The formula is in the blue highlighted column on Sheet "Social Upload". The 3 other involved columns are on the "Social Upload" and "MULTI Product Info" sheets, highlighted in orange.

Cells H2 through H4 aren't returning the correct adjacent cell values and I don't underestand why...

 

[DanteAmor]

Try this

{=IFERROR(INDEX('MULTI Product Info'!$B$2:$B$100,MAX(IF(--ISNUMBER(SEARCH(IF('MULTI Product Info'!$E$2:E$100<>"",'MULTI Product Info'!$E$2:E$100),H2)),ROW('MULTI Product Info'!$E$2:E$100)))-1),"No match")}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.


You can create a named range, select the range of cells from E2 to E100 on the "MULTI Product Info" sheet, in the name box type sku.
Now you modify the formula to this:
{=IFERROR(INDEX('MULTI Product Info'!$B$2:$B$100,MAX(IF(--ISNUMBER(SEARCH(IF(sku<>"",sku),H2)),ROW(sku)))-1),"No match")}


--------------
Note: Check your "MULTI Product Info" sheet, you have a million unoccupied rows, delete those rows and your file will be smaller.

 

[majesticoj]

@DanteAmor Thank you again! That took care of that.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.