On Sat, 25 Aug 2007 06:26:01 -0700, Abdullah Kajee
wrote:

Text in cell A1 " the quick brown fox"
Text in cel A2 " June is here"
Text in cell A3 " Today is Monday" and so on until row A55000.
I have a spreadsheet with thousands of lines but some lines of text repeats
as above. How do I write one IF statement that searches for one of three
words such as "brown", "here" and "Monday" and then returns the keyword that
I am looking for. This way, I can quickly filter on all lines that have the
word Monday or the word June etc..I know I can use
=IF(ISNUMBER(SEARCH(.......but help me to understand how to write 3 criteria
or more in one statement...right now I can only write one criteria ie
=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE"


You can nest up to seven IF's:

=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE",
IF(ISNUMBER(SEARCH("Brown",A2)),"Brown","not found"))

You can also use an array argument for the search term in your search
statement:

=CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH({"Brown";"here" ;"Monday"},L1:L55000)),0),"Brown","Here","Monda y")

ARRAY-Entered with <ctrl<shift<enter will return the key word for the first
match.


But what if multiple key words are present? If in your 55000 lines, you might
have BROWN and HERE present.

You might want to investigate the Advanced Filter under the Data Menu.
--ron

Thank you so much Ron...I will try the nesting bit...the CHOOSE function
worked beautifully...the only problem is that I am getting a lot of #NA
results where there is no match...how do I write into the formula to leave
blank if no match?

"Ron Rosenfeld" wrote:

On Sat, 25 Aug 2007 06:26:01 -0700, Abdullah Kajee
wrote:

Text in cell A1 " the quick brown fox"
Text in cel A2 " June is here"
Text in cell A3 " Today is Monday" and so on until row A55000.
I have a spreadsheet with thousands of lines but some lines of text repeats
as above. How do I write one IF statement that searches for one of three
words such as "brown", "here" and "Monday" and then returns the keyword that
I am looking for. This way, I can quickly filter on all lines that have the
word Monday or the word June etc..I know I can use
=IF(ISNUMBER(SEARCH(.......but help me to understand how to write 3 criteria
or more in one statement...right now I can only write one criteria ie
=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE"


You can nest up to seven IF's:

=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE",
IF(ISNUMBER(SEARCH("Brown",A2)),"Brown","not found"))

You can also use an array argument for the search term in your search
statement:

=CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH({"Brown";"here" ;"Monday"},L1:L55000)),0),"Brown","Here","Monda y")

ARRAY-Entered with <ctrl<shift<enter will return the key word for the first
match.


But what if multiple key words are present? If in your 55000 lines, you might
have BROWN and HERE present.

You might want to investigate the Advanced Filter under the Data Menu.
--ron

On Sat, 25 Aug 2007 07:12:02 -0700, Abdullah Kajee
wrote:

Thank you so much Ron...I will try the nesting bit...the CHOOSE function
worked beautifully...the only problem is that I am getting a lot of #NA
results where there is no match...how do I write into the formula to leave
blank if no match?

There are many ways. One method:

=IF(ISNA(your_formula),"",your_formula)

and I think you're better off with the nesting approach. I've run into some
glitches using the SEARCH array approach and I don't have time to figure it out
right now.


--ron

RON,
Just to THANK YOU again and to let you know that I used the nesting of the
IF(ISNUMBER formula with a criteria to leave "" (blank) if no matches and it
works. Thank you very much...it is wonderful to have such a global community
for knowledge and information sharing. This is truly a connected community.

"Ron Rosenfeld" wrote:

On Sat, 25 Aug 2007 06:26:01 -0700, Abdullah Kajee
wrote:

Text in cell A1 " the quick brown fox"
Text in cel A2 " June is here"
Text in cell A3 " Today is Monday" and so on until row A55000.
I have a spreadsheet with thousands of lines but some lines of text repeats
as above. How do I write one IF statement that searches for one of three
words such as "brown", "here" and "Monday" and then returns the keyword that
I am looking for. This way, I can quickly filter on all lines that have the
word Monday or the word June etc..I know I can use
=IF(ISNUMBER(SEARCH(.......but help me to understand how to write 3 criteria
or more in one statement...right now I can only write one criteria ie
=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE"


You can nest up to seven IF's:

=IF(ISNUMBER(SEARCH("JUNE",A2)),"JUNE",
IF(ISNUMBER(SEARCH("Brown",A2)),"Brown","not found"))

You can also use an array argument for the search term in your search
statement:

=CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH({"Brown";"here" ;"Monday"},L1:L55000)),0),"Brown","Here","Monda y")

ARRAY-Entered with <ctrl<shift<enter will return the key word for the first
match.


But what if multiple key words are present? If in your 55000 lines, you might
have BROWN and HERE present.

You might want to investigate the Advanced Filter under the Data Menu.
--ron

On Sat, 25 Aug 2007 07:24:01 -0700, Abdullah Kajee
wrote:

RON,
Just to THANK YOU again and to let you know that I used the nesting of the
IF(ISNUMBER formula with a criteria to leave "" (blank) if no matches and it
works. Thank you very much...it is wonderful to have such a global community
for knowledge and information sharing. This is truly a connected community.

You're very welcome. Thanks for the feedback.
--ron