Lookup Column Shows Deleted Items: Root Cause and Fix

When you delete a list item that is referenced by a lookup column in SharePoint, the lookup column continues to display the deleted item. This behavior causes confusion because users see entries in a lookup field that no longer exist in the source list. The root cause is that SharePoint lookup columns store a static reference to the item ID, not a live query to the source list. This article explains why deleted items appear in lookup columns and provides a step-by-step method to remove them.

Why Deleted Items Still Appear in Lookup Columns

A lookup column in SharePoint does not run a live query against the source list each time the column is rendered. Instead, when you add a lookup column to a list, SharePoint creates a hidden relationship between the two lists. The lookup column stores the ID of the selected item from the source list. When you delete that source item, the lookup column retains the ID. SharePoint displays the original value in the lookup field because it cannot find a matching item in the source list. This is not a bug; it is the designed behavior of SharePoint list relationships.

The issue becomes more visible when you use a lookup column in a choice field or a filtered view. Users see an entry that appears valid but leads to a broken link. The only way to remove the deleted item from the lookup column is to delete the lookup value from every item that references it. Manual removal is time-consuming if the lookup column is used in hundreds of items. A PowerShell script can automate this task.

PowerShell Method to Remove Deleted Lookup References

The following steps use the SharePoint PnP PowerShell module. This module is free and works with SharePoint Online and SharePoint 2019 on-premises. You need SharePoint admin permissions to run the script.

Install and Connect the PnP PowerShell Module

1. Install PnP PowerShell
   Open Windows PowerShell as an administrator. Run the command Install-Module PnP.PowerShell -Scope CurrentUser. Press Y to confirm the installation from PSGallery.
2. Connect to your SharePoint site
   Run Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive. A browser window opens. Sign in with a SharePoint admin account.

Run the Script to Remove Deleted Lookup Values

1. Identify the source list and the deleted item ID
   Go to the source list. Open the list settings and note the list title. If you do not know the deleted item ID, run Get-PnPListItem -List "SourceListName" to list all current items.
2. Run the cleanup script
   Use the following script. Replace “SourceListName” with the name of the source list, “TargetListName” with the name of the list that contains the lookup column, “LookupColumnName” with the internal name of the lookup column, and “DeletedItemID” with the ID of the deleted item.

   $items = Get-PnPListItem -List "TargetListName" -PageSize 500
foreach ($item in $items) {
  if ($item["LookupColumnName"] -eq $DeletedItemID) {
    Set-PnPListItem -List "TargetListName" -Identity $item.Id -Values @{"LookupColumnName" = $null}
  }
}

   This script loops through all items in the target list. For each item where the lookup column equals the deleted item ID, it sets the lookup value to null. The change removes the broken reference and the deleted item no longer appears in the lookup column.

3. Verify the result
   Open the target list in a browser. Click any item that previously showed the deleted item. The lookup column should now be empty. If the column is required, you must select a valid item from the source list.

If Lookup Column Still Shows Deleted Items After the Script

The script above removes the lookup value from all items in the target list. If the deleted item still appears, one of the following scenarios applies.

Lookup Column References a Different List Than Expected

A lookup column can reference any list in the same site. The source list might not be the one you think. Check the lookup column settings. Go to the target list settings, select the lookup column, and note the “Get information from” field. That is the actual source list. Run the script with the correct source list name.

Deleted Item Is in the Site Collection Recycle Bin

When you delete an item from a list, SharePoint moves it to the site collection recycle bin. The item is soft-deleted. The lookup column still sees the item ID because the item still exists in the recycle bin. To remove the reference permanently, delete the item from the recycle bin. Go to the source list, click the gear icon, select “Site contents”, then open the “Site collection recycle bin” from the top menu. Find the deleted item, select it, and click “Delete selection”. After that, run the PowerShell script again.

Lookup Column Uses Multiple Values

A lookup column set to allow multiple values stores an array of item IDs. The script above checks for an exact match. If the lookup column contains multiple IDs and only one of them is the deleted item, the script sets the entire column to null. To preserve other values, you need a more advanced script that filters the array. Use the following modification:

$items = Get-PnPListItem -List "TargetListName" -PageSize 500
foreach ($item in $items) {
  $lookupValues = $item["LookupColumnName"]
  if ($lookupValues -is [array] -and $lookupValues -contains $DeletedItemID) {
    $newValues = $lookupValues | Where-Object { $_ -ne $DeletedItemID }
    Set-PnPListItem -List "TargetListName" -Identity $item.Id -Values @{"LookupColumnName" = $newValues}
  }
}

Preventive Measures for Lookup Columns

You cannot prevent users from deleting items in the source list. But you can reduce the impact of broken lookup references by using the following practices.

Use a Dedicated Lookup Source List

Create a separate list that contains only the values used by lookup columns. Restrict permissions on that list so only site owners can add or delete items. This reduces the chance that a user accidentally deletes a referenced item.

Enable Relationship Behavior

SharePoint list relationships can enforce referential integrity. In the source list settings, under “Advanced settings”, enable “Allow lookup fields that reference this list”. Then set “Enforce relationship behavior” to “Cascade Delete”. When you delete an item from the source list, SharePoint automatically deletes all related items in the target list. This option is available only for lists in the same site collection. Be careful: Cascade Delete removes entire list items, not just the lookup value. Test this behavior on a copy of your data first.

PowerShell Manual Cleanup vs Automated Cleanup: Key Differences

Item	Manual Cleanup	Automated Cleanup (PowerShell)
Time required	Several hours for hundreds of items	Minutes for thousands of items
Error risk	High — human error in selecting wrong items	Low — script targets exact item ID
Skill needed	None	Basic PowerShell knowledge
Multiple value support	Manual editing of each item	Script with array filtering
Recycle bin handling	User must check recycle bin manually	Script does not check recycle bin

You can now remove deleted items from lookup columns using PowerShell. Run the script after verifying the source list and the deleted item ID. For ongoing prevention, enable Cascade Delete on the source list or restrict permissions on the lookup source list. If you need to handle multiple-value lookup columns, use the array filtering script provided in this article.