A common SharePoint request is to create a lookup field from a list in another site. This is possible so long as the sites are within the same site collection, but it does require some customization.
Since I am dealing with a single-server farm, I was able to do this fairly quickly in PowerShell:
# Set the site collection in which the source and destination webs exist
$site = Get-SPSite("https://mysite/path/")
# Set the source web to the web where the source list exists
$sourceweb = $site.OpenWeb("sourcepath")
# Set the destination web to the web where you wish to add the lookup
$destinationweb = $site.OpenWeb("destinationpath")
# Set the source list to the list in which the field exists
$sourcelist = $sourceweb.Lists["List Name"]
# Set the source field to the field you wish to perform lookups on
$sourcefield = $sourcelist.Fields["Field Name"]
# Get the collection of all fields in the destination list
$destinationfields = $destinationweb.Lists["List Name"].Fields
# Add a lookup field to the fields collection
# See the SPFieldCollection AddLookup(String,Guid,Guid,Boolean) method
# For details: http://msdn.microsoft.com/en-us/library/ms430950.aspx
$lookupname = $destinationfields.AddLookup($sourcefield.Title,$sourcelist.ID,$sourceweb.ID,false)
# The above produces an error: Missing expression after ','. At line:1, char:83
# Right--in Powershell, $false is False.
$lookupname = $destinationfields.AddLookup($sourcefield.Title,$sourcelist.ID,$sourceweb.ID,$false)
# Get the newly-added lookup field
$lookup = $destinationfields.GetFieldByInternalName($lookupname)
# Set the lookup field to the internal name of the source field
$lookup.LookupField = $sourcefield.InternalName
# Update the lookup -- without this the previous changes won't be saved
$lookup.Update()
# Dispose of the SPWeb and SPSite objects
$sourceweb.Dispose()
$destinationweb.Dispose()
$site.Dispose()
This works, but you still can’t query cross-site lookup columns which was my issue.