Index/Match - Return value if partial text from cell contained in separate column

[excelnewb221]

Hello,

Hoping for some help. Unfortuanately the Xl2bb Addin is disabled on my computer by Admin access.

I have 2 columns.
Column A, contains the source data. This column contains a string of words.
Column B will contain the formula.
Column C contains a string of words. These values may or may not be in Column A.
However, if any word from the cell in column C, matches any word from a cell in column A, please return that value from column A, next to its respective cell from column C.

This is basically what I'm trying to accomplish:

Data (column A)	Formula (column B)	Original Value (column C)	Formula will produce:
Apple Carrot Orange		Orange Carrot	Apple Carrot Orange
Beet Tomato Onion		Tomato	Beet Tomato Onion
Pepper Banana Grape		Grape Juice	Pepper Banana Grape
Lettuce Kale Chia		Kale Spinach Broccoli	Lettuce Kale Chia
Apple Carrot Orange		Orange Carrot	Apple Carrot Orange
Beet Tomato Onion		Beet Juice	Beet Tomato Onion
Pepper Banana Grape		Strawberry	N/A
Lettuce Kale Chia		Kale	Lettuce Kale Chia
Apple Carrot Orange		Orange	Apple Carrot Orange
Beet Tomato Onion		Juice	N/A

And below are the terrible attempts I've made. I clearly don't know how to make this work.
Not really sure the best approach to take, so hoping someone could help guide me in that regard.
Please let me know what is needed to better articulate what I'm trying to achieve. Really appreciate any help.

Data	Formula Column	Formula Written	Original Value	Value for Formula to Work	Problem
Apple Carrot Orange	Beet Tomato Onion	INDEX(A2:A5,MATCH(E2,A2:A5))	Orange Carrot	Orange Carrot	Index/Match - returns value from 2nd row?
Beet Tomato Onion	Beet Tomato Onion	VLOOKUP(E3&"*",A:A,1,FALSE)	Beet Juice	Beet	Vlookup - only returns if first word matches?
Pepper Banana Grape	Pepper Banana Grape	VLOOKUP(E4&"*"&"*",A:A,1,FALSE)	Grape Juice	Pepper	Vlookup - only returns if first word matches?
Lettuce Kale Chia	Lettuce Kale Chia	INDEX(A2:A5,MATCH("*"&E5,A2:A5,0))	Kale Spinach Broccoli	Chia	Index/Match - only returns if last word matches
Apple Carrot Orange	0​	XLOOKUP(TRUE,ISNUMBER(SEARCH(D6:D9,A6)),D6:D9,0)	Orange Carrot
Beet Tomato Onion	0​	FILTER(D6:D9,ISNUMBER(SEARCH(D6:D9,A6)),0)	Beet Juice
Pepper Banana Grape	#N/A​	MATCH(TRUE,ISNUMBER(SEARCH(D6:D9,A6)),0)	Grape Juice
Lettuce Kale Chia	#N/A​	XLOOKUP("*"&D9&"*",A6:A9,A6:A9,,2)	Kale Spinach Broccoli		Only works if column C contains one word
Apple Carrot Orange	#N/A​	INDEX(A7:A10,MATCH("*"&D10,A7:A10,0))	Apple Orange
Beet Tomato Onion	#N/A​	INDEX(A8:A11,MATCH("*"&D11&"*",A8:A11,0))	Beet Juice

 

[kweaver]

How about this approach?

Book6
	A	B	C	D
1	Data (column A)	Formula (column B)	Original Value (column C)	Formula will produce:
2	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Apple Carrot Orange
3	Beet Tomato Onion	Beet Tomato Onion	Tomato	Beet Tomato Onion
4	Pepper Banana Grape	Pepper Banana Grape	Grape Juice	Pepper Banana Grape
5	Lettuce Kale Chia	Lettuce Kale Chia	Kale Spinach Broccoli	Lettuce Kale Chia
6	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Apple Carrot Orange
7	Beet Tomato Onion	Beet Tomato Onion	Beet Juice	Beet Tomato Onion
8	Pepper Banana Grape	N/A	Strawberry	N/A
9	Lettuce Kale Chia	Lettuce Kale Chia	Kale	Lettuce Kale Chia
10	Apple Carrot Orange	Apple Carrot Orange	Orange	Apple Carrot Orange
11	Beet Tomato Onion	N/A	Juice	N/A
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IF(0<>SUM(--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2))),A2,"N/A")

 

[excelnewb221]

How about this approach?

Book6
	A	B	C	D
1	Data (column A)	Formula (column B)	Original Value (column C)	Formula will produce:
2	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Apple Carrot Orange
3	Beet Tomato Onion	Beet Tomato Onion	Tomato	Beet Tomato Onion
4	Pepper Banana Grape	Pepper Banana Grape	Grape Juice	Pepper Banana Grape
5	Lettuce Kale Chia	Lettuce Kale Chia	Kale Spinach Broccoli	Lettuce Kale Chia
6	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Apple Carrot Orange
7	Beet Tomato Onion	Beet Tomato Onion	Beet Juice	Beet Tomato Onion
8	Pepper Banana Grape	N/A	Strawberry	N/A
9	Lettuce Kale Chia	Lettuce Kale Chia	Kale	Lettuce Kale Chia
10	Apple Carrot Orange	Apple Carrot Orange	Orange	Apple Carrot Orange
11	Beet Tomato Onion	N/A	Juice	N/A
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IF(0<>SUM(--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2))),A2,"N/A")

this is absolutely incredible. i've tested it out a bunch, and it's perfect. thank you so much.
if you have more time, do you think you can explain how the formula works? it's beyond me, but would love to understand it.

 

[kweaver]

Do these 2 examples with the step-by-step help?

Book6
	A	B	C	D	E	F	G
1	Data (column A)	Formula (column B)	Original Value (column C)	Transpose portion of A2	Search	Isnumber	Sum
2	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Orange	14	1	2
3	Beet Tomato Onion	Beet Tomato Onion	Tomato	Carrot	7	1
4	Pepper Banana Grape	Pepper Banana Grape	Grape Juice
5	Lettuce Kale Chia	Lettuce Kale Chia	Kale Spinach Broccoli
6	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot
7	Beet Tomato Onion	Beet Tomato Onion	Beet Juice	Transpose for A8
8	Pepper Banana Grape	N/A	Strawberry	Strawberry	#VALUE!	0	0
9	Lettuce Kale Chia	Lettuce Kale Chia	Kale
10	Apple Carrot Orange	Apple Carrot Orange	Orange
11	Beet Tomato Onion	N/A	Juice
Sheet1

Cell Formulas
Range	Formula
D2:D3,D8	D2	=TRANSPOSE(TEXTSPLIT(C2," "))
E2:E3,E8	E2	=SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2)
F2:F3,F8	F2	=--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2))
G2,G8	G2	=SUM(--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2)))
B2:B11	B2	=IF(0<>SUM(--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2))),A2,"N/A")
Dynamic array formulas.

If you go to the Formula tab, you can click on Evaluate Formula and step into it.

 

[excelnewb221]

Do these 2 examples with the step-by-step help?

Book6
	A	B	C	D	E	F	G
1	Data (column A)	Formula (column B)	Original Value (column C)	Transpose portion of A2	Search	Isnumber	Sum
2	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Orange	14	1	2
3	Beet Tomato Onion	Beet Tomato Onion	Tomato	Carrot	7	1
4	Pepper Banana Grape	Pepper Banana Grape	Grape Juice
5	Lettuce Kale Chia	Lettuce Kale Chia	Kale Spinach Broccoli
6	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot
7	Beet Tomato Onion	Beet Tomato Onion	Beet Juice	Transpose for A8
8	Pepper Banana Grape	N/A	Strawberry	Strawberry	#VALUE!	0	0
9	Lettuce Kale Chia	Lettuce Kale Chia	Kale
10	Apple Carrot Orange	Apple Carrot Orange	Orange
11	Beet Tomato Onion	N/A	Juice
Sheet1

Cell Formulas
Range	Formula
D2:D3,D8	D2	=TRANSPOSE(TEXTSPLIT(C2," "))
E2:E3,E8	E2	=SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2)
F2:F3,F8	F2	=--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2))
G2,G8	G2	=SUM(--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2)))
B2:B11	B2	=IF(0<>SUM(--ISNUMBER(SEARCH(TRANSPOSE(TEXTSPLIT(C2," ")),A2))),A2,"N/A")
Dynamic array formulas.

If you go to the Formula tab, you can click on Evaluate Formula and step into it.

View attachment 95015

very helpful thanks again. i'll work through it all. such a powerful formula.

 

[kweaver]

You can remove the TRANSPOSE. Out of force of habit, I used that initially, but you don't need it.

Book6
	B
1	Formula (column B)
2	Apple Carrot Orange
3	Beet Tomato Onion
4	Pepper Banana Grape
5	Lettuce Kale Chia
6	Apple Carrot Orange
7	Beet Tomato Onion
8	N/A
9	Lettuce Kale Chia
10	Apple Carrot Orange
11	N/A
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IF(0<>SUM(--ISNUMBER(SEARCH(TEXTSPLIT(C2," "),A2))),A2,"N/A")

 

[Kevin_Li]

I have another idea

 

[DanteAmor]

You can remove the TRANSPOSE

You can also remove <>0, if the sum returns > 0 it will be true, then:

=IF(SUM(--ISNUMBER(SEARCH(TEXTSPLIT(C2,," "),A2))),A2,NA())

 

[Kevin_Li]

工作簿2
	A	B	C	D
1	Data (column A)	Formula (column B)	Original Value (column C)	Formula will produce:
2	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Apple Carrot Orange
3	Beet Tomato Onion	Beet Tomato Onion	Tomato	Beet Tomato Onion
4	Pepper Banana Grape	Pepper Banana Grape	Grape Juice	Pepper Banana Grape
5	Lettuce Kale Chia	Lettuce Kale Chia	Kale Spinach Broccoli	Lettuce Kale Chia
6	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot	Apple Carrot Orange
7	Beet Tomato Onion	Beet Tomato Onion	Beet Juice	Beet Tomato Onion
8	Pepper Banana Grape	#N/A	Strawberry	N/A
9	Lettuce Kale Chia	Lettuce Kale Chia	Kale	Lettuce Kale Chia
10	Apple Carrot Orange	Apple Carrot Orange	Orange	Apple Carrot Orange
11	Beet Tomato Onion	#N/A	Juice	N/A
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=IF(COUNT(FIND(TEXTSPLIT(C2," "),A2)),A2,NA())

 

[excelnewb221]

Sorry everyone. Really appreciate all the knowledge shared.
Looks like I made a big mistake - I neglected to mention that the rows do not match up directly.

Is there a way to have the formula search all throughout column A? And if there is a match, then it returns the respective value that it found in column A.

I tried changing the formula to:

…(C2,“ ”),A:A))),A:A,“N/A”)
But that clearly isn’t going to work.

Sorry again to make things complicated.

@kweaver
@Kevin_Li