SharePoint Calculated Column to compare two Person fields

[Kim Williams]

Is it possible to create a calculated column to compare Person Field A with Person Field B?

[#2]

Certainly it is! you can create a calculated column to compare two Person fields using a simple formula. Here's is a step-by-step guide:

1. Go to your SharePoint site and navigate to the list or library where you want to add the calculated column.

2. Click on the "Settings" gear icon and select "List Settings" or "Library Settings".

3. Under the "Columns" section, click on "Create column".

4. Give your calculated column a name and select the column type as "Calculated (calculation based on other columns)".

5. In the "Formula" section, enter the following formula:

=IF([Person Field A]=[Person Field B],"Equal","Not Equal")

Replace `[Person Field A]` and `[Person Field B]` with the actual column names you want to compare.

6. Choose "Single line of text" as the data type for your calculated column.

7. Click "OK" to save the calculated column.

After adding the calculated column, SharePoint will evaluate the formula for each item in the list or library. It will display "Equal" if Person Field A is equal to Person Field B, and "Not Equal" if they are different.

Feel free to adjust the formula based on your specific requirements.

[#3]

@AdminAy Those Person fields are not available to choose from to do the comparison and if I manually add them, it gives me this error, "One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas."  Seems like I would need to copy those Person fields into a single line column and then compare.

[#4]

Ok, but use a SharePoint workflow or a Power Automate flow to automatically copy the values from the Person fields to the corresponding single line of text columns whenever changes occur.
This will ensure that the text columns stay up-to-date with the Person field values.
Once the values are copied, then you can apply the formular

[#5]

@Kim Williams It it not possible using SharePoint calculated columns as formulas will not support person fields.

What is the use case after comparing the person fields?

If you just want to show the text (equal/not equal) in your list view, you can use JSON formatting like: 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$Person1.email] == [$Person2.email], 'Equal', 'Not Equal')",
  "style": {
    "font-weight": "bold",
    "color": "=if([$Person1.email] == [$Person2.email], 'green', 'red')"
  }
}

Where Person1 and Person2 are internal names of your person columns. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online 

Output: 

Documentation: SharePoint JSON column formatting 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#6]

@ganeshsanap My use case is if the person that has created the new document (Requestor) is the same person that is marked as the Reviewer, I want it to set my Status field to a specific value, if they are not the same person, then I want an Approver sent to the Reviewer. If I use the "Is Equal" JSON, can I use that value Not Equal or Equal in my flow?

[#7]

@Kim Williams JSON formatting is used to just show the text in list view, it does not actually store any value in the list item level.

However, you can compare the person column values in flow as well using equals() operator.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.