SharePoint Online: Add Lookup Column to List using PowerShell

Requirement: Create a new Lookup Column to a SharePoint Online List.

How to Create Lookup Column in SharePoint Online List?

The Lookup column in SharePoint Online lets you populate the column value based on another column’s value from a different list on the same site. To add a lookup field, follow these steps:

  • Browse to your SharePoint Online site and navigate to the target list in which you want to add a lookup column.
  • Under the List tab, click on the “Create Column” button in the ribbon.
  • Provide the Name to your new column, specify the type as “Lookup” 
    Add Lookup column to SharePoint Online List using PowerShell
  • Select the source list and field for lookup, specify other optional values such as column description, required field value, etc., and click on “OK” to create a lookup field in SharePoint Online list.

PowerShell to Add Lookup Column to List in SharePoint Online:

Lookup columns are helpful when you want to associate a list with another list or when you want to look up values from another list. Let’s see how to add a lookup column to a list in SharePoint Online using PowerShell.

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add column to list
Function Add-LookupColumnToList()
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description=[string]::Empty,
        [Parameter(Mandatory=$false)] [string] $IsRequired = "FALSE",
        [Parameter(Mandatory=$false)] [string] $EnforceUniqueValues = "FALSE",
        [Parameter(Mandatory=$true)] [string] $LookupListName,
        [Parameter(Mandatory=$true)] [string] $LookupField

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        #Get the web, List and Lookup list
        $Web = $Ctx.web
        $List = $Web.Lists.GetByTitle($ListName)
        $LookupList = $Web.Lists.GetByTitle($LookupListName)

        #Check if the column exists in list already
        $Fields = $List.Fields
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
            Write-host "Column $Name already exists in the List!" -f Yellow
            #Get IDs of Lookup List and Web
            $LookupListID= $
            #sharepoint online powershell create lookup field
            $FieldSchema = "<Field Type='Lookup' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' EnforceUniqueValues='$EnforceUniqueValues' List='$LookupListID' WebId='$LookupWebID' ShowField='$LookupField' />"
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)

            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message

#Set parameter values
$ListName="Project Milestone"
$DisplayName="Parent Project"
$Description="Select the Parent Project from the List"
$LookupListName="Projects" #Parent List to Lookup

#Call the function to add column to list
Add-LookupColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -LookupListName $LookupListName -LookupField $LookupField
Tips: For Multi-Lookup Field, simply change the Field type from “Lookup” to “LookupMulti” and add an attribute Mult=’TRUE’ to the $FieldSchema variable !

PnP PowerShell to Add Lookup Column to SharePoint Online List

Let me show you how to quickly add a lookup column to a SharePoint list using PnP PowerShell.

$SiteURL = ""
$ListName= "Projects"
$DisplayName = "Parent Project"
$InternalName = "ParentProject"

$ParentListName="Parent Projects"
$LookupField = "Title" #Internal Name

#Connect to the site
Connect-PnPOnline -Url $SiteURL -Interactive        

#Add Lookup Field to the List    
Add-PnPField -List $ListName -Type Lookup -DisplayName $DisplayName -InternalName $InternalName -AddToDefaultView

#Set Field Properties
Set-PnPField -List $ListName -Identity $InternalName -Values @{LookupList=(Get-PnPList $ParentListName).Id.ToString(); LookupField=$LookupField}

We can also add a lookup column with the Field schema XML:

#Config Variables
$SiteURL = ""
$ListName= "Projects"
$ParentListName="Parent Projects"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

#Get the ID of the Parent List
$LookupListID = (Get-PnPList -Identity $ParentListName).ID

#Define XML Schema for URL Field
$FieldXML= "<Field Type='Lookup' Name='ParentProject' ID='$([GUID]::NewGuid())' DisplayName='Parent Project' List='$LookupListID' ShowField='Title'></Field>"

#Add Lookup Field to list
Add-PnPFieldFromXml -FieldXml $FieldXML -List $ListName

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

Your email address will not be published. Required fields are marked *