I am trying to put a formula together that will look at a cell and return a value based on a partial word, but to do it with multiple criteria. For example:

If A1 has the following in it:
Type of session: Private

If A2 has:
Type of session: Group

In another cell I want to then search A1 for private and if it finds it return something, like for example 1. But at the same time, search A1 for group and if it finds it, return 2 for example. If it finds none of my criteria, it can return 0 for example.


1 Answer 1


Use regexmatch() and Boolean arithmetic, like this:

=regexmatch(A1, "(?i)Private") + 2 * regexmatch(A1, "(?i)Group")

  • Thanks for your comment. And in this case, could I return different text for each instead of a number? and how would for formula look if I wanted to add two more conditions to it? Commented Jun 6, 2022 at 21:30
  • Very nice approach.
    – Blindspots
    Commented Jun 6, 2022 at 22:26
  • I gather that this is your first question at Stack Exchange. Please ask only one question per post. See What should I do when someone answers my question? Commented Jun 7, 2022 at 8:07
  • @Hazel try this formula for your new request: =IFNA(IFS( regexmatch(A1, "(?i)Private"),"text1", regexmatch(A1, "(?i)Group"),"text2", regexmatch(A1, "(?i)Other"),"text3", regexmatch(A1, "(?i)Another"),"text4" ))
    – Daniele
    Commented Jun 7, 2022 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.