Using Lookup Field in Calculated Column SharePoint

Using Lookup Field in Calculated Column SharePoint

If you tried to use Lookup Field in Calculated Column SharePoint Formula, you will note that the lookup column is not listed in calculated column formula in SharePoint as shown below:

Using Lookup Field in SharePoint Calculated Column Formula

In this post, we’ll learn how to use Lookup Field value in Calculated Column by exploring the following:

You might also like to read Using ID Field in Calculated Column in SharePoint.


Can’t Use lookup field in Calculated Column SharePoint Formula

First of all, you should be aware of the lookup field can’t be referenced in

  • SharePoint Calculated Column Formula, and
  • SharePoint Column Validation.

Actually, it’s by design, there are some fields that unsupported to use in calculated column like

Check the full list of supported and unsupported fields in SharePoint Calculated Column Formula.

Lookup Field in Calculated Column SharePoint Formula

In this section, we’ll provide some of the available workarounds that helps you to use Lookup Field in Calculated Column SharePoint Formula.

  • Using Choice Field instead of a Lookup field.
  • Using SharePoint Designer Workflow.
  • Build SharePoint calculated column formula using Custom code

Using Choice Field instead of Lookup field in Calculated Column

Actually, this workaround mainly depends on your business requirement. If you can replace the lookup field to a choice field with static options, so it’s preferred to use the choice field as a supported field in the SharePoint Calculated Column instead of the lookup field.

Note: Choice field with multiple selections is not supported in the SharePoint Calculated Column, for more details, please Check The Supported and Unsupported Columns in SharePoint Calculated Column Formula.

Using SharePoint Designer Workflow

You can use SharePoint workflow as a workaround solution to be able to use the value of lookup field in Calculated Column.

Simply, you can do the following:

  • Create a new lookup field with a supported data type like a Single line of text.
  • In the workflow, Set the new lookup field value with the original Lookup column value on item created or changed.
  • Now you can use the new lookup field in the SharePoint calculated column Formula.

Steps

  • Open your list, Create a new Field with an appropriate name like “LookupValue” with a single text data type.
Using Lookup Field in SharePoint
  • Hide the new “LookupValue” column from showing in the New Form and Edit form Using PowerShell.
hide and show fields in SharePoint lists forms using PowerShell
$rootWeb = Get-SPWeb http://siteURL
#Get a reference to the  List
$list=$rootWeb.Lists["List Name"]
#Get a reference to the created field
$Field = $list.Fields["Column Name"]
#Hide the column in New and Edit Mode
$Field.ShowInEditForm = $false
$Field.ShowInNewForm = $false
#Push field Update
$Field.Update()

You might also like to read Show and Hide Columns in SharePoint List Forms Using PowerShell

  • Open SharePoint Designer > Workflow > add a new list workflow > Select your list.
  • Provide an appropriate name for your workflow.
  • In Actions > Below List actions > select Set Field in Current Item.
  • Select your new field “LookupValue” and set it to the current List Lookup column.
  • Make sure that the workflow will start on Item created/changed.
  • Save & Publish your Workflow.
Using Lookup Field in SharePoint Calculated Column Formula using workflow

Please note that, In SharePoint workflow, don’t forget to return the field as Lookup value (as text) for single lookup value as shown below:

Using single Lookup Field in SharePoint Calculated Column Formula

If you are using Multiple Lookup Values, so you should return the field as Lookup values, comma delimited as shown below:

Using multiple Lookup Field in SharePoint Calculated Column Formula
  • Now, it’s the time to test your workflow, by adding a new item in your list,
  • You should note that the lookup value column (as a single text) is equal to the Lookup field value.
Lookup Field in Calculated Column
  • Open your List > List Setting > List column > Create a new Calculated Column.
  • You will note that the newly created field “Lookup value” as a single text is listed in the columns that can be used in the calculated column formula.
  • Add it, and save the column setting.
Lookup field in calculated column SharePoint
  • Go back to add a new List item,
  • You should note that the lookup value is reflected to the calculated column.
Using Lookup Field in SharePoint Calculated Column Formula

Build SharePoint calculated column formula using Custom code

In case, you have a development background, you can build your formula in event receiver or using JavaScript/ JSOM.

Build SharePoint calculated column formula using JSOM

Using JSOM, you can build your custom formula based on lookup selection or Text change then you can set the calculated value to a new field.

The below articles would help you to get started

If you would like to disable this field to avoid any change for your calculation, Please check Disable SharePoint Field in Edit Form.

Build SharePoint calculated column formula Using SharePoint Event Receiver

Try to create an Event Receiver on item added or updated to

  • Build your formula via code instead of using the calculated column.
  • Or, to set the value of the lookup field to another text field that can be used in the calculated column formula.

Check how to create SharePoint receiver at Create an event receiver.


Applied To
  • SharePoint Online.
  • SharePoint 2019.
  • SharePoint 2016.
  • SharePoint 2013.
  • SharePoint 2010.
Conclusion

In conclusion, we have explained How to use Lookup field in Calculated Column Formula in SharePoint by providing the below workarounds:

  • Using Choice Field instead of a Lookup field.
  • Using SharePoint Designer Workflow.
  • Build SharePoint calculated column formula using Custom code.
You might also like to read
Have a Question?

If you have any related questions, please don’t hesitate to Ask it at deBUG.to Community.

8 thoughts on “Using Lookup Field in Calculated Column SharePoint”

Leave a Reply