Look up part text from list and return value

[gobblechops]

Hi,

Column A contains descriptions.

A table in columns E to F, contains... Part texts in column E, contains values to return in column F.

Column B requires the formula. E.g Formula required in Cell B1..... Look in column E (table) for any part text contained in the full text of cell A1, if finding a matching part text, return corresponding value in column F of (table).

Thanks!

 

[ExceLoki]

try this

=IF(COUNTIF(A1,"*"&E1&"*"),F1,"")

 

[Sufiyan97]

Another option

Book2
	A	B	C	D	E	F	G
1	Full Text	Formula			Partial Text	Value to Return
2	I am an Excel user	MS Excel			Excel	MS Excel
3	I am a Word user	MS Word			Word	MS Word
4					#N/A	#N/A
5					#N/A	#N/A
6					#N/A	#N/A
7					#N/A	#N/A
8					#N/A	#N/A
9					#N/A	#N/A
10					#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(INDEX($F$2:$F$100,AGGREGATE(15,6,(ROW($F$2:$F$100)-ROW($F$2)+1)/ISNUMBER(SEARCH($E$2:$E$100,A2)),1)),"")

 

[gobblechops]

Yes Sufiyan97, that has worked! Top man. Thanks.

 

[Sufiyan97]

You're welcome!
Glad we could help.