SharePoint Calculated Column by searching another column for various strings

Question

I am working with a SharePoint 2016 list that contains one column with names and other values. I want to use a calculated column to search that "name" column and return another value if that "name" column contains specific text.

As an example, the "name" column might have something like "Jones|Angela|aj77869;Smith|John|js55634." There are a lot of different names in all of these rows in the list. If the column contains certain text, say "Jones" or "Smith" or "Davis" or "Bergman" then I want a calculated column to display "Manager." There are other options for the calculated column too, so it could be "manager" or "staff" or "executive" based on the names in the "name" column.

What I have tried and am having trouble with is the following formula in the calculated column:

=IF(OR(ISNUMBER(SEARCH({"Smith","Johnson","Williams","Jones","Brown","Davis","Miller","Wilson","Moore"},[ Approvers]))),"Manager",IF(OR(ISNUMBER(SEARCH({"Taylor","Anderson","Thomas","Jackson","White","Harris","Martin","Thompson","Garcia","Martinez"},[ Approvers]))),"Staff",IF(OR(ISNUMBER(SEARCH({"Banks","Bailey","Bower","Allen","Crockett","Gilbert","Hall","Hernandez","Lewis","Lee","King","Clark"},[ Approvers]))),"Executive",IF(OR(ISNUMBER(SEARCH({"Wright","Lopez","Khan","Scott","Green","Adams","Baker"},[ Approvers]))),"C-level","Name Not Found"))))

It works great in Excel, where I tested it on the extracted SharePoint list. When I paste it into SharePoint it returns a lot of "Name Not Found" even though the text is in the "name" column. For example, for the rows with "bsmith" in it, all are coming back as "Name Not Found."

I'm just not seeing what the issue is. Thanks!

Answer 1

Per my test and knowledge, the search/Find function in SharePoint list simply does not support such feature to search for multiple "Find text" in a column. Only the first word will be searched.

Example: The formula i use in my sample test is like this:=Search({"Riverside","King County"},Address)

Here is what I get:

According to your requirement, you want to auto populate the column value according to the value in the approvers column which may contains many names. (I assume there will not be a scenario like a manger and a staff will be in the cell at the same time). MS Power Automate may be an option for you to check for the data.

Something for your reference:https://powerusers.microsoft.com/t5/Building-Flows/How-to-include-a-condition-to-check-multiple-values-in-a-column/td-p/171923