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:

Table of Contents

1 Can’t Use lookup field in Calculated Column SharePoint Formula
2 Lookup Field in Calculated Column SharePoint Formula

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

ID, (Check how to use ID Field in Calculated Column in SharePoint).
Lookup field.
Multiple lines of text
Choice field with multiple selections.

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.

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&amp;nbsp; 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.

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.

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.