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]

From your posted example, what would you expect the results to be?
It would be helpful if you re-post and example and expected results (again, please use XL2BB).

 

[excelnewb221]

Yes, you are correct kweaver. I will repost, but the addin doesn’t work in my computer.
Sorry for the trouble.

 

[kweaver]

Use the same approach as you did before. Just don't send a picture (jpg, etc.). Those are useless, frankly.

 

[DanteAmor]

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.

So, assuming the following:

Dante Amor
	A	B	C
1	Data	Formula	Original Value
2	Apple Carrot Orange	Apple Carrot Orange	Orange Carrot
3	Beet Tomato Onion	Beet Tomato Onion	Tomato
4	Pepper Banana Grape	Pepper Banana Grape	Grape Juice
5	Lettuce Kale Chia	Lettuce Kale Chia	Kale Spinach Broccoli
6		Apple Carrot Orange	Orange Carrot
7		Beet Tomato Onion	Beet Juice
8		#VALOR!	Strawberry
9		Lettuce Kale Chia	Kale
10		Apple Carrot Orange	Orange
11		#VALOR!	Juice
Sheet

Put this formula in cell B2 (No need to copy down.)

=LET(a,A2:A5,BYROW(C2:C11,LAMBDA(bx,INDEX(a,UNIQUE(MAX(IFERROR(BYROW(TEXTSPLIT(bx,," "),LAMBDA(br,MATCH("*"&br&"*",a,0))),-ROW($A$1))))+ROW($A$1)-1))))

Adjust A2:A5 for the cells that have the Data and C2:C11 for Original value.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[excelnewb221]

Yes, you are correct kweaver. I will repost, but the addin doesn’t work in my computer.
Sorry for the trouble.

this video from mrexcel helps solve the issue i was having with XL2BB:

 

[Kevin_Li]

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

Cell Formulas
Range	Formula
B2:B11	B2	=LOOKUP(1,0/BYROW(N(ISNUMBER(FIND(TEXTSPLIT(C2," "),A$2:A$11))),LAMBDA(x,SUM(x))),A$2:A$11)

 

[Kevin_Li]

Or if you don't have lambda, try this

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

Cell Formulas
Range	Formula
B2:B11	B2	=LOOKUP(1,0/MMULT(N(ISNUMBER(FIND(TEXTSPLIT(C2," "),A$2:A$11))),ROW(INDIRECT("1:"&COUNTA(TEXTSPLIT(C2," "))))^0),A$2:A$11)