IF function help, multiple values

[MikeGauntlett]

Hi Guys,

I am trying to create an IF function that serches a destination cell for set words.

I need to search this cell for for a variety of words and to return those words into the cell the formula is typed in

this is what i have so far

=IF(ISNUMBER(SEARCH("*PPG*",D2)),"PPG","")

Where D2 is the search cell and PPG is the search criteria,

if the set words do not appear they are to return blank

hope this makes sense

any ideas to search for multiple independant words ?

 

[Jonmo1]

Welcome to the board...

Try this

Excel Workbook
	D	E	F	G
1	Sentence to search	*	*	List of Keywords
2	This is the sentence to search	Search	*	This
3	Here is one more sentence	Is	*	Is
4	None are found	*	*	The
5	And this will be one more	Is	*	List
6	*	*	*	Of
7	*	*	*	Words
8	*	*	*	To
9	*	*	*	Search
10	*	*	*	For
Sheet1

Note the result in E5, It found the word "is", instead of "this"
Note the result in E2, where there are multiple words in the sentence, the one listed last in the G2:G10 list will be returned.


Hope that helps.

 

[Soggy]

You could use ahelper column with the list of words you are searching for (in this case J) ans use this.

=IF(ISERROR(VLOOKUP("*"&D2&"*",J:J,1,0))=FALSE,D2,"")

It will bring back the value in D2. If it is the word found you need then this does that:

=IF(ISERROR(VLOOKUP("*"&D2&"*",J:J,1,0))=FALSE,VLOOKUP("*"&D2&"*",J:J,1,0),"")

 

[MikeGauntlett]

I am so confused

the search cells wont really be a sentance but a part description

 

[MikeGauntlett]

Will this work

=IF(ISNUMBER(SEARCH("*PPG*",A1)),"PPG",IF(ISNUMBER(SEARCH("*HENKEL*",A1)),"HENKEL",IF(ISNUMBER(SEARCH("*AKZO*",A1)),"AKZO",IF(ISNUMBER(SEARCH("*CHEMETALL*",A1)),"CHEMETALL",""))))

where the seacrch values are henkel ppg chemetall and akzo

?

 

[Jonmo1]

Sure, that works..

But so does this

=LOOKUP(REPT("z",LEN(A1)),CHOOSE({1,2},"",LOOKUP(2^15,SEARCH($B$1:$B$4,A1),$B$1:$B$4)))

B1 = henkel
B2 = ppg
B3 = akzo
B4 = chemetall

 

[MikeGauntlett]

So with

=LOOKUP(REPT("z",LEN(A1)),CHOOSE({1,2},"",LOOKUP(2^15,SEARCH($B$1:$B$4,A1),$B$1:$B$4)))

I need to have a list of the search criteria in a separte column then give them index numbers B1,2,3 and so on ?

 

[Soggy]

Not quite column B contains the search criteria so cell B1 has henkel cell b2 has ppg etc.

 

[Jonmo1]

Yes, but..

Don't look at it as "I have to do...."

It's actually a benefit to keep your list of key words in a seperate range of cells.
It's much easier to maintain/adjust as your criteria may change
Rather than having to change the formula, you can just modify the contents of the range of cells.

 

[MikeGauntlett]

=LOOKUP(REPT("",LEN(AA2)),CHOOSE({1,2},"",LOOKUP(2^15,SEARCH(H2,AA2),H2)))

Can the above be adapted to populate the destination cell with the search value AA2 ? if that makes sense