1

For example, in Excel cells, I have these texts

A1 "Hi_how_are_you_today_?"

A2 "I_am_doing_great"

A3 "everything good at you workplace?"

A4 "Doesn't have any of these words"

I am looking for 3 words How, Great and workplace. In any cell if any of these words is found then it should return the same word, else it should a return blank value.

I was able to write this formula on Excel but it is returning #N/A error if any of these values aren't found. =IFS(ISNUMBER(SEARCH("How",A1))=TRUE,"How",ISNUMBER(SEARCH("Workplace",A1))=TRUE,"Workplace",ISNUMBER(SEARCH("great",A1))=TRUE,"great")

Can we make some changes in this formula so it will return blank if any of these is not found?

Chandrakant
  • 21
  • 1
  • 1
  • 4

2 Answers2

2

You can also try

=IFERROR(INDEX({"how","great","workplace"},MATCH(TRUE,ISNUMBER(SEARCH({"how","great","workplace"},A1)),0)),"")
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
1

Simply add a condition that will always be true at the end:

=IFS(ISNUMBER(SEARCH("How",A1)),"How",ISNUMBER(SEARCH("Workplace",A1)),"Workplace",ISNUMBER(SEARCH("great",A1)),"great", TRUE, "")
                                                                                                                         ^^^^^^^^

Also, I dropped the =TRUE in the formula, since they are unneeded.

Jerry
  • 70,495
  • 13
  • 100
  • 144
  • can you elaborate the logic behind this condition? it is working perfectly. – Chandrakant Dec 12 '17 at 09:30
  • @ChandrakantGaur It is very much like using 'else' in any other programming language. The first conditions are evaluated first, but if none of the previous evaluated to 'true', then you want the last to evaluate to 'true' no matter what. You could say that the last condition is saying something like 'did the previous conditions fail?' If true, then proceed. I was just googling a bit since it's a new formula, and found that the [microsoft support](https://support.office.com/en-gb/article/IFS-function-36329a26-37b2-467c-972b-4a39bd951d45) webpage also uses that. – Jerry Dec 12 '17 at 09:37
  • One last thing I should have pointed out earlier: you can drop all the `=TRUE` in the formula without it functioning differently. – Jerry Dec 12 '17 at 09:39