given a reference of data, Extract Text from one cell

[dodo1051]

so i have a cell contains text string and i want to extract out the string with the reference i have,
i know we can use FIND or SEARCH but i only know to use it for a text string not a range of cell.

So from column A,
Reference data is D1:D3
to column B

_____________A________________B__________C__________D__________
|1|Apple, Pineapple, Tiger | Tiger    |    |Cat
|2|Mantis, Card, Table     | Mantis   |    |Tiger
|3|Pineapple, Cat, Card    | Cat      |    |Mantis
|4|

 

[steve the fish]

Hi. Try to paste data directly from excel as we can then copy and paste it back to excel. That said this should work for you, place in B1 and drag down:

=INDEX($D$1:$D$3,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$1:$D$3,A1)),0),0))

 

[steve the fish]

And heres a slightly more complex one which gets rid of the potentially incorrect results which may occur with apple and pineapple:

=INDEX($D$1:$D$3,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(","&$D$1:$D$3&",",SUBSTITUTE(","&A1&","," ",""))),0),0))

 

[jtakw]

Hi,

Another option, Use B1 formula or C1 formula for in case there's no match found, either formula copied down.

Book1
	A	B	C	D
1	Apple, Pineapple, Tiger	Tiger	Tiger	Cat
2	Mantis, Card, Table	Mantis	Mantis	Tiger
3	Pineapple, Cat, Card	Cat	Cat	Mantis
Sheet331

Cell Formulas
Range	Formula
B1	=LOOKUP(2,1/SEARCH(" "&D$1:D$3&","," "&A1&","),D$1:D$3)
C1	=IFERROR(LOOKUP(2,1/SEARCH(" "&D$1:D$3&","," "&A1&","),D$1:D$3),"")

 

[dodo1051]

Hi,
Thank you so much!

 

[jtakw]

You're welcome, from a month ago...