# IF function help, multiple values

#### MikeGauntlett

##### New Member
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 ?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Welcome to the board...

Try this

Excel Workbook
DEFG
1Sentence to search**List of Keywords
2This is the sentence to searchSearch*This
3Here is one more sentenceIs*Is
4None are found**The
5And this will be one moreIs*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.

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),"")

I am so confused

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

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

?

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

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 ?

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

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.
Rather than having to change the formula, you can just modify the contents of the range of cells.

=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

Replies
1
Views
93
Replies
14
Views
793
Replies
9
Views
608
Replies
2
Views
162
Replies
7
Views
90

1,203,396
Messages
6,055,163
Members
444,767
Latest member
bryandaniel5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back