Excel Formula | Filter out a word from a table of words in another sheet Hi MS Community,

[wktay]

Hi MS Community,

I have the following worksheet. In the Orders Tab, I need to be able to filter out the "Fruit" from the "Sentence".

Sheet1 = Fruits

Sheet2 = Orders

In Fruits:

	A
1	apple
2	pear
3	banana

In Orders:

	Sentence	Fruit
1	James buy apple	apple
2	Peter sell pear	pear
3	Banana from japan	banana
4	apple keeps the doctor away	apple
5	Do you sell durian?	Invalid

Preferably, casing can be ignored. I was trying out Index & Match but it didn't work (pls ignore the rows and column number as I was trying to simply using fruits).

=INDEX(fruit!A1:A3,MATCH("*"&fruit!A1&"*",C1:C4,0))

Kind Regards

[#2 · Accepted solution]

@wktay 

here are 2 options:

=LET(a,TEXTJOIN(",",1,IF(ISNUMBER(SEARCH($I$1:$I$3,A1)),$I$1:$I$3,"")), IF(a<>"",a,"invalid"))

or

=IFERROR(TEXTJOIN(",",1,TEXTSPLIT(LOWER(A1),TEXTSPLIT(LOWER(A1),LOWER($I$1:$I$3),,1),,1)),"invalid")

where A1 is the sentence cell and I1:I3 is the list of fruit

notice that the first uses SEARCH so case ignored but needed the extra step if nothing found. in the second I could enclose an IFERROR but needed to convert everything to LOWER() to make case indepenent

[#3]

Thanks for the respond.

Just a question regarding the "$I$1:$I$3" portion.
If from the "Fruit" table, can I exclude some rows?
For below example, I want to exclude the "chicken"
So like "$I$1:$I$3 & $I$5:$I$6""

The reason I can't change the "Fruits" table is becuz it is used for other things.

A
1 apple
2 pear
3 banana
4 chicken
5 orange
6 kiwi

Thanks in advance

[#4]

yes but not like that. either create a 'helper table' where you do that or use something like:
VSTACK($I$1:$I$3,$I$5:$I$6)
CHOOSEROWS($I$3:$I$6,1,2,3,5,6)
FILTER($I$3:$I$6, $I$3:$I$6<>"chicken")
in the 1st option above you can do any of the above options as another variable in the LET so something like:
=LET(list, CHOOSEROWS($I$3:$I$6,1,2,3,5,6),
a,TEXTJOIN(",",1,IF(ISNUMBER(SEARCH(list,A1)),list,"")),
IF(a<>"",a,"invalid")
)

[#5]

Cool, thanks for your help!

[#6]

@m_tarler 

Sorry I need ask another question after testing the code. I notice it shows all fruits in the sentence.

Is it possible to just have only 1 fruit, but have the flexibility to adjust which if necessary? 

	Sentence	Fruit
1	James buy apple and pear	apple
2	Banana and durian for lunch	Durian

Kind Regards

[#7]

Yes I specifically added the TEXTJOIN to show all the results. You could replace with INDEX( ... ,1) to only take the first but not sure what you mean by "flexibility to adjust which". I suppose you could just have them listed in priority of showing.
=LET(a,INDEX(IF(ISNUMBER(SEARCH($I$1:$I$3,A1)),$I$1:$I$3,""),1), IF(a<>"",a,"invalid"))
or
=IFERROR(INDEX(TEXTSPLIT(LOWER(A1),TEXTSPLIT(LOWER(A1),LOWER($I$1:$I$3),,1),,1),1),"invalid")

[#8]

Hello @m_tarler , I'm back again.

So I tested around your formula and came across another issue. If you can see the table sample below, it should explain. Basically the code I should get from the Contract should be NJM, but becuz of the "Japan", it got JA as well.

Is it possible to ensure that the casing are matching as well? (Since all the codes are in uppercase).

Im using the "=LET" by the way

Tyvm

ListofCode

NJM

MPS

32

JA

E6

E6M

Contract	getCode
Naphtha C&F Japan Mini [NJM] - ICE NJM Block Nov-24	NJM, JA

[#9]

yes, simply replace SEARCH with FIND and it will be case sensitive.

[#10]

tyvm!