SharePoint Online: Get Lookup Column Value using PowerShell

Requirement: Get Lookup field value in SharePoint Online using PowerShell.

Scenario: We have a parent list called “Parent Projects” and a child list called “Project Milestones”. The “Parent Project Name” field in the child list is being looked up from the parent list’s “Project Name” field. Here is my PowerShell script to get lookup field values.

sharepoint online get set lookup field values using powershell

PowerShell to Get Lookup Field Value in SharePoint Online:

To retrieve the value of the lookup column in a SharePoint Online list, use this 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"

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/Sites/PMO"
$ListName="Project Milestones"
$FieldName="ParentProjectName" #Internal Name
$ListItemID="25"

#Get Credentials to connect
$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 List Item
$List = $Ctx.Web.lists.GetByTitle($ListName)
$ListItem = $List.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get Lookup Field Value
$Lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$ListItem[$FieldName]
Write-host $Lookup.LookupValue

SharePoint Online: Retrieve Multi-Value Lookup Field Values using PowerShell:

When “Allow Multiple Values” is enabled, it becomes a multi-valued lookup column. So the script to read the lookup column goes here.

#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"

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/PMO"
$ListName="Projects"
$FieldName="ParentProjects" #Internal Name
$ListItemID="25"

#Get Credentials to connect
$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 List Item
$List = $Ctx.Web.lists.GetByTitle($ListName)
$ListItem = $List.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get Multi-Lookup Field Value
$LookupValueCollection = [Microsoft.SharePoint.Client.FieldLookupValue[]]$ListItem[$FieldName]

Foreach ($LookupValue in $LookupValueCollection)
{
    write-host $LookupValue.LookupValue 
}

PnP PowerShell to Retrieve Lookup Field Value

Here is the PnP PowerShell way to get the lookup field value.

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Retail"
$ListName = "Projects"
$LookupFieldName = "Parent_x0020_Project" #Internal Name

#Connect to SharePoint Online
Connect-PnPOnline -URL $SiteURL -Interactive

#Get the first list item
$ListItem = Get-PnPListItem -List $ListName -PageSize 500 | Select -First 1

#Get the Lookup Field value
$ListItem[$LookupFieldName].LookupValue

Similarly, to get all values from the multivalued lookup field, use the following:

$ListItem[$LookupFieldName].LookupValue -join "; "

Here is another post on updating lookup column value in SharePoint Online using PowerShell Update Lookup Field using PowerShell in SharePoint Online

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

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