Using Lookup Field in SharePoint Calculated Column Formula

In this article, I will explain

How to use Lookup Field in SharePoint Calculated Column Formula

Scenario

I have a list with a lookup field, I would like to use this lookup field in in SharePoint Calculated Column Formula but unfortunately, it’s not listed in the available formula column as shown below:Using Lookup Field in SharePoint Calculated Column Formula

Cause

It’s by design, The lookup column can’t be used in SharePoint Calculated Column Formula and SharePoint Column Validation.

Check The Supported and Unsupported Columns in SharePoint Calculated Column Formula.

Workarounds

Using Choice Field instead of Lookup field

If you can change the lookup field to choice field with static options, so it’s recommended to use the choice field instead of lookup field, the choice field is supported in the Calculated Column. Check The Supported and Unsupported Columns in SharePoint Calculated Column Formula.

Using SharePoint Designer Workflow
  • 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.
Using Code.

In case you have a development background,  you can also use the Event Receiver to build your formula via code.

In this article, I will explain the workflow workaround steps.

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 Calculated Column Formula

  • Hide the new “LookupValue” column from showing in the New Form and Edit form Using PowerShell.

hide and show fields in 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()

  • 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

Note: In the workflow,

For single lookup value use the following:

Using Lookup Field in SharePoint Calculated Column Formula

For Multiple lookup values use the following

Lookup values, comma delimited

  • Test your workflow, by adding a new item, you should note that the lookup value column is equal to the Lookup field value.

Using Lookup Field in SharePoint Calculated Column Formula test workflow

  • Open your List > List Setting > List column > Create a new Calculated Column.
  • Add the listed new field “Lookup value” in your formula > save the column setting.

Lookup in calculated column SharePoint

  • Add new List item, you should note that the NewID=ID and reflected to the calculated column.

Using Lookup Field in SharePoint Calculated Column Formula Final.gif

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

In this article, I have explained How to use Lookup field in SharePoint Calculated Column Formula.

See Also
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s