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!