If text string contains a word defined in an array of search words, then return a defined text

[nielsnielsen]

Dear all wizards,

I have a list of cells with text strings (notes) and I need to check each cell if it contains a word from the search list. Each note will only contain one of the search words. If a note contain a search word, the function need to return a Level 1 text and a Level 2 text. If a note does not contain any of the search Words, the function must return "No L1" and "No L2".
The note can contain the search word in any part of the textstring and it can contain both letters, numbers and basic signs such as " . - , _ "


Thanks.


Example:

	A	B	C	D	E	F	G	H
1	Note	Level 1	Level 2			Search word	Level 1 text	Level 2 text
2	11SomeTextAA	One	First			AA	One	First
3	22SomeText BB	One	Second			BB	One	Second
4	DD some text	Two	First			CC	One	Thrid
5	Some BD Text	No L1	No L2			DD	Two	First
6	55 SomeText FF	Two	Third			EE	Two	Second
7	Text no letters	No L1	No L2			FF	Two	Thrid
8						-	No L1	No L2

<tbody>
</tbody><colgroup><col><col><col span="4"><col><col><col></colgroup>

Notes in: A2:A7
Return of Level 1 in: B2:B7
Return of Level 2 in: C2:C7
Search words in: F2:F8
Level 1 return text in: G2:G8
Level 2 return text in: H2:H8

-The search words and their return text can be in other sheet...

 

[AlphaFrog]

Put this in B2 and drag down column B
=IF(ISNUMBER(SEARCH(F2,A2)),G2,"No L1")

Put this in C2 and drag down column C
=IF(ISNUMBER(SEARCH(F2,A2)),H2,"No L2")

 

[Marcelo Branco]

Welcome to Mr Excel

Try this

Formula in B2 copied across to C2 and down
=IFERROR(LOOKUP(9.99E+307,SEARCH($F$2:$F$7,$A2),G$2:G$7),G$8)

Hope this helps

M.

 

[nielsnielsen]

Hi AlphaFrog,

Thanks for your reply. However, this does only work if the search Word is on the same row as the note. So, in this example it only Works for row 1+2, not 4-7
-I also added =IF(ISNUMBER(SEARCH($F$2:$F$7,A2)),$G$2:$G$7,"No L1") - But same result

I need it to search the entire list of search Word, so the row number of note and search Word does not have an influence..


Thanks.

 

[nielsnielsen]

Marcelo Branco! You are the true wizard!!

Thanks, it works perfectly (so far as I can tell)!!

Thanks mate.

 

[István Hirsch]

Give this a try:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Note	Level 1	Level 2	*	*	Search word	Level 1 text	Level 2 text
2	11SomeTextAA	One	First	*	*	AA	One	First
3	22SomeText BB	One	Second	*	*	BB	One	Second
4	DD some text	Two	First	*	*	CC	One	Thrid
5	Some BD Text	No L1	No L2	*	*	DD	Two	First
6	55 SomeText FF	Two	Thrid	*	*	EE	Two	Second
7	Text no letters	No L1	No L2	*	*	FF	Two	Thrid
8	*	*	*	*	*	-	No L1	No L2
Sheet

 

[Marcelo Branco]

Marcelo Branco! You are the true wizard!!

Thanks, it works perfectly (so far as I can tell)!!

Thanks mate.

You are welcome. Glad to help

M.