How to find Multiple Keywords in one cell and Return Multiple Results for each

[atorres05]

Hey guys and gals,

First time posting... I don't even know if this is possible but I know I'm not the man to figure it out.
I want to search a single cell (A1) for three different keywords and return a corresponding value for each keyword found using nested IF functions.

This is what I have so far:
=IF(ISNUMBER(FIND("Keyboard", A1)), "one", IF(ISNUMBER(FIND("laptop", A1)),"two",IF(ISNUMBER(FIND("desk", A1)),"three")))

A1 contains all three keywords but when I run the function it only picks up the first keyword and returns "one". Should I be using an AND function somewhere in this crazy mess?

Thanks so much guys.

 

[GlennUK]

Am guessing at the exact logic you are after, but what about:
=IF(ISNUMBER(FIND("Keyboard", A1)), "one ") & IF(ISNUMBER(FIND("laptop", A1)),"two ") & IF(ISNUMBER(FIND("desk", A1)),"three")))

 

[atorres05]

This is exactly what I need, thank you very much!

Only thing is now the function returns FALSE if it does not find one of the keywords in the target cell.
For example: FALSE two three

What do I have to change so it returns nothing if it does not find one of the keywords. So, just "two three" if 'Keyboard' is not found.

 

[atorres05]

Sorry, stupid question. I figured it out right away.

=IF(ISNUMBER(FIND("Keyboard",A1)),"one "," ")&IF(ISNUMBER(FIND("laptop",A1)),"two "," ")&IF(ISNUMBER(FIND("desk",A1)),"three "," ")

 

[Rick Rothstein]

Here is another formula you can use...

=IFERROR(CHOOSE((8+SEARCH(A1,"keyboard,laptop  ,desk"))/9,"one","two","three"),"")

Note: There are 8 total characters for the first two words in the first quoted text string (spaces are used to fill out the 8 characters when the word is shorter than 8 total characters)... the last word does not need to be filled out to 8 characters because it comes at the end of the text.