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

[excelnewb221]

Hello.
This is round 2.
I posted the question yesterday, and got a terrific response, but i neglected to mention one thing -
The current formula is incredible, but works on a row by row basis.
Index/Match - Return value if partial text from cell contained in separate column

however, I forgot to mention that I need the formula to search all throughout column A. If there is a match, then it returns the respective value that it found, whichever row it was in, within column A.

I am not able to install the XL2BB addin due to admin rights unfortunately.

as i type this out now, I'm not how it would be possible, especially when there are redundant words in column A,
but I would greatly appreciate any help to help in figuring this out.

thanks again. please let me know if I can explain this in a better way.

Data (column A)	Formula (column B)	Lookup Value (column C)
Apple Carrot Orange	Apple Carrot Orange	Orange
Beet Tomato Onion	Pepper Banana Grape	Banana
Pepper Banana Grape	Lettuce Kale Chia	Kale
Lettuce Kale Chia	N/A	Spinach
Apple Carrot	Lettuce Kale Chia	Chia
Beet Tomato	Beet Tomato	Tomato
Pepper Banana Grape	N/A	Strawberry
Kale Chia	Beet Tomato & Beet Tomato Onion	Beet
Apple Carrot Orange	Lettuce Kale Chia & Kale Chia	Kale
Tomato Onion	N/A	Juice

 

[DanteAmor]

I sent the formula in post #14

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

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...

www.mrexcel.com

=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))))

 

[DanteAmor]

In column C are you only going to have one word?

 

[DanteAmor]

If in column C you only have one word.

First, I recommend that in column A you only have unique values:

varios 11jul2023.xlsm
	A	B	C
1	Data (column A)	Formula (column B)	Lookup Value (column C)
2	Apple Carrot Orange	Beet Tomato Onion & Beet Tomato	Beet
3	Beet Tomato Onion	Pepper Banana Grape	Banana
4	Pepper Banana Grape	Lettuce Kale Chia & Kale Chia	Kale
5	Lettuce Kale Chia		Spinach
6	Apple Carrot	Lettuce Kale Chia & Kale Chia	Chia
7	Beet Tomato	Beet Tomato Onion & Beet Tomato & Tomato Onion	Tomato
8	Kale Chia		Strawberry
9	Tomato Onion	Beet Tomato Onion & Beet Tomato	Beet
10		Lettuce Kale Chia & Kale Chia	Kale
11			Juice
12		Apple Carrot Orange	Orange
Hoja4

In cell B2 put the following formula (You don't need to copy it down)

=BYROW(C2:C12,LAMBDA(br,TEXTJOIN(" & ",,IF(ISNUMBER(SEARCH(br,A2:A9)),A2:A9,""))))

 

[excelnewb221]

In column C are you only going to have one word?

View attachment 95076

@DanteAmor , thank you so much for your help.
I tried your solution in Post #14 and i love it. I'm tinkering around with it, and even if column A doesn't contain unique values, the value returned by the formula should help flag the item and will basically alert me that I should look into the item more, which i'm happy to do manually, since the formula takes care of all the hard work.
I'll spend a few days toying around with this. but this was extremely helpful. Thanks again.

To answer your question:
Column C will be a variable and change every time I need to use this file. Most likely 95% of the time, column C will contain multiple words.
Column A is actually a list of about 1,000 classifications, the list is static and cannot change.
Column A contains multiple words, and also acronyms. Unfortunately, the column cannot contain only unique values.

 

[excelnewb221]

Thanks for this solution as well Dante. This has been a great learning experience for me.
I'm toying around with this formula, and I like how it can produce two results.
I'm also seeing why you recommend only having unique values in column A.

I'm trying to think if there's a better way I should have phrased my request.
I can still make elaborate use of both your formula in tandem, but wonder if there's a way to do this that would better help place the orange highlights cells below.
if you don't mind, I may have to come back with more questions in a day or two.

while the below rules are the same:
Column C will be a variable and change every time I need to use this file. Most likely 95% of the time, column C will contain multiple words.
Column A is actually a list of about 1,000 classifications, the list is static and cannot change.
Column A contains multiple words, and also acronyms. Unfortunately, the column cannot contain only unique values.

Partial text lookup.xlsx
	A	B	C
1	Data	Formula	Original Value
2	Apple Carrot Orange	Apple Carrot Orange	Carrot
3	Beet Tomato Onion	Beet Tomato Onion & Onion	Onion
4	Pepper Banana Grape		Beet Juice
5	Lettuce Kale Chia	Lettuce Kale Chia	Chia
6	Beet	Beet Tomato Onion & Beet	Beet
7	Onion		Onions
8	Pepper Banana	#N/A	Banana
9	Pepper Banana Jack		Lettuce Chia Kale
Solution 1 (2)

If in column C you only have one word.

First, I recommend that in column A you only have unique values:

varios 11jul2023.xlsm
	A	B	C
1	Data (column A)	Formula (column B)	Lookup Value (column C)
2	Apple Carrot Orange	Beet Tomato Onion & Beet Tomato	Beet
3	Beet Tomato Onion	Pepper Banana Grape	Banana
4	Pepper Banana Grape	Lettuce Kale Chia & Kale Chia	Kale
5	Lettuce Kale Chia		Spinach
6	Apple Carrot	Lettuce Kale Chia & Kale Chia	Chia
7	Beet Tomato	Beet Tomato Onion & Beet Tomato & Tomato Onion	Tomato
8	Kale Chia		Strawberry
9	Tomato Onion	Beet Tomato Onion & Beet Tomato	Beet
10		Lettuce Kale Chia & Kale Chia	Kale
11			Juice
12		Apple Carrot Orange	Orange
Hoja4

In cell B2 put the following formula (You don't need to copy it down)

=BYROW(C2:C12,LAMBDA(br,TEXTJOIN(" & ",,IF(ISNUMBER(SEARCH(br,A2:A9)),A2:A9,""))))