0

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.

3

1 Answer 1

1

Use regexmatch() and Boolean arithmetic, like this:

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

4
  • 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? Jun 6 at 21:30
  • Very nice approach. Jun 6 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? Jun 7 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
    Jun 7 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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