IF(OR(ISNUMBER(SEARCH("Text") Function

[Kelli Webb]

Hi, as per my formula below it is picking up all text that I've "" stated, and all else as z - Other Roles.

The issue is I need to pick up "Legal Partner" but not Transformation Legal Partner.

How do I get my formula to exclude Transformation Legal Partner??

=IF(OR(ISNUMBER(SEARCH("Branch Manager",A2)),ISNUMBER(SEARCH("Legal Partner",A2))),A2,"z - Other Roles")

 

[Nebu]

Hi:

May be this?

Thanks

 

[jayalaxmi]

Hi, as per my formula below it is picking up all text that I've "" stated, and all else as z - Other Roles.

The issue is I need to pick up "Legal Partner" but not Transformation Legal Partner.

How do I get my formula to exclude Transformation Legal Partner??

=IF(OR(ISNUMBER(SEARCH("Branch Manager",A2)),ISNUMBER(SEARCH("Legal Partner",A2))),A2,"z - Other Roles")

Hi Kelli,

This is what you are looking for:

=IFS(A2="Legal Partner","Legal Partner",A2="Branch Manager","Branch Manager",TRUE,"z - Other Roles")

Regards
Jaya

 

[Peter Bartholomew]

In future, with Office 365 dynamic arrays, the solution could be
= IF( OR(Roles=@Titles), @Titles, "Other roles" )

As it is
= IF( found?, Title, "Other roles" )
will work provided 'Title' is a relative reference
= Sheet1!$A2
and found? is a named formula that refers to
= OR(Roles=Title)

This last step is a device that bypasses the traditional Excel worksheet behaviour of destroying array relationships.

 

[Kelli Webb]

Hi Kelli,

This is what you are looking for:

=IFS(A2="Legal Partner","Legal Partner",A2="Branch Manager","Branch Manager",TRUE,"z - Other Roles")

Regards
Jaya

Hi jayalaxmi, I'm still on Excel 2013 and I don't believe the IFS functionality work in it.
thanks anyway! I greatly appreciate it
Kelli

 

[Kelli Webb]

In future, with Office 365 dynamic arrays, the solution could be
= IF( OR(Roles=@Titles), @Titles, "Other roles" )

As it is
= IF( found?, Title, "Other roles" )
will work provided 'Title' is a relative reference
= Sheet1!$A2
and found? is a named formula that refers to
= OR(Roles=Title)

This last step is a device that bypasses the traditional Excel worksheet behaviour of destroying array relationships.

thanks Peter, this appears to be functioning as required!
I love your work!!