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]

Wow. My apologies for not reviewing my thread title before submitting!!! I would edit the thread title but it appears I cannot do so.

This should have been the title: Need formula to find partial string match in a separate column and return adjacent value.

 

[DanteAmor]

Try this option

Book1
	A	B	C	D	E	F
1			glue-4-red	book	A	C
2			book-2-green	gum	B	A
3			pencil-3-brown	glue	C	D
4				pencil	D
5
Sheet1

Cell Formulas
Range	Formula
F1:F3	F1	{=IFERROR(INDEX($E$1:$E$4,MAX((IFERROR(SEARCH($D$1:$D$4,C1),-1))*(ROW($D$1:$D$4)))),"No match")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[majesticoj]

@DanteAmor Thank you! I'm not sure but I think your formula is trying to find an exact match for C1 in column D...?

I probably wasn't clear before. Here is the exact scenario I have for which I'm trying to make a formula. I think this context will help everyone better understand.

I need to match product filenames (column C) to SKUs (column D) and return the adjacent product name (column E).

Example: The formula should "match" product-redsocks1-small (C2) with redsocks1 (D4) and return adjacent cell value Red Socks (E4).

(Ultimately, I'm trying to get the formula to match photo file names in column C with the matching product name (for the photo/file) in column E)

Example table here.

 

[DanteAmor]

I think you made a mistake on file, this is what you sent.
You must put the expected result and explain why that result.

Book1
	A	B	C	D	E	F
1			glue-4-red	book	gum
2			book-2-green	gum	glue
3			pencil-3-brown	glue	book
4
Sheet1

 

[majesticoj]

@DanteAmor

I have linked to a new file here.

I need to match product filenames (column C) to SKUs (column D) and return the adjacent product name (column E).

Here is an example with expected result: The formula should "match" product-redsocks1-small (C2) with redsocks1 (D4) and return adjacent cell value Red Socks (E4).

Ultimately, I'm trying to get the formula to match photo file names in column C with the matching product name (for the photo/file) in column E.

 

[DanteAmor]

This is the result of my formula with your data.
But the result is not clear, since you have the same data in column D and column E.

Book1
	A	B	C	D	E	F	G
1			glue-4-red	book	gum	book
2			book-2-green	gum	glue
3			pencil-3-brown	glue	book
4
Sheet1

Cell Formulas
Range	Formula
F1	F1	{=IFERROR(INDEX($E$1:$E$3,MAX((IFERROR(SEARCH($D$1:$D$3,C1),-1))*(ROW($D$1:$D$3)))),"No match")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

That's why I proposed some data.
I will change them, the formula remains the same.

Book1
	A	B	C	D	E	F	G
1			glue-4-red	book	today	tomorrow
2			book-2-green	gum	yesterday
3			pencil-3-brown	glue	tomorrow
4
Sheet1

Cell Formulas
Range	Formula
F1	F1	{=IFERROR(INDEX($E$1:$E$3,MAX((IFERROR(SEARCH($D$1:$D$3,C1),-1))*(ROW($D$1:$D$3)))),"No match")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

-------------------------
Remember, it is an array formula, to accept you must press Shift + Control + Enter

 

[DanteAmor]

I have linked to a new file here.

You uploaded the same file.
Check the formula again in post #7

 

[majesticoj]

@DanteAmor

Thanks again!

The file I uploaded again had different table values but the same file name in that directory as before... Maybe I should have changed the file name when saving...

I did try your instructions again and this time, used your instructions about this being an 'array formula' and it worked!

Thank you!!!

 

[DanteAmor]

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