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!

1 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:

enter image description here

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

  • Power Automate isn't an option for me. It sounds like this isn't possible on SharePoint then.
    – sk00ter
    May 27, 2020 at 23:42

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.