Others

How to Populate a Lookup Column with Power Automate

Kushani Nimanthika Oct 9, 2026 869 views

Question details

The user needs to update an empty lookup column in Microsoft Lists or SharePoint based on matching values found in two different lists.

How to Populate a Lookup Column with Power Automate

Product: Power Automate
Device & OS: not provided
Scenario: Automating list updates by querying a secondary list and populating a lookup column in the primary list when a specific value matches.
Observed behavior: The lookup column requires dynamic population through an automated workflow rather than manual data entry.

Before you start

Ensure you have the necessary edit permissions for both SharePoint lists and access to create custom automated cloud flows in Power Automate.

Solution 1Recommended

Create a Custom Flow to Compare and Update Values

Build a custom Power Automate flow that compares values between both lists and updates the lookup column with the correct reference ID.

To update a lookup column in SharePoint or Microsoft Lists, you cannot simply pass the text value. You must pass the internal integer ID of the item from the source list. The flow will need to fetch items, compare the matching criteria, and apply the ID to the lookup field.

Set the flow trigger

Open Power Automate, create an automated cloud flow, and choose a trigger such as 'When an item is created or modified' for your primary destination list.

Retrieve list items

Add a 'Get items' action to fetch data from your secondary source list where the matching reference values reside.

Compare list values

Add an 'Apply to each' loop and insert a 'Condition' action to compare the target value (e.g., the TP value) from your primary list with the values fetched from the secondary list.

Update the lookup column

Inside the 'If yes' branch of the condition, add an 'Update item' action for the primary list. Locate your lookup column field and insert the 'ID' dynamic content from the secondary list.

Create a Custom Flow to Compare and Update Values

SharePoint Lookup IDs: SharePoint lookup columns strictly require the referenced item's integer ID. Attempting to map text strings into the lookup field will cause the flow to fail.

Solution 2

Request Custom Flow Design from the Community

If your list structure involves complex data types or multiple matching criteria, seek tailored flow design help from the Microsoft Community.

Free Microsoft Office alternative

Handle Data Lookups Efficiently with WPS Office

While Power Automate manages advanced cloud list workflows, WPS Spreadsheet offers a powerful, lightweight, and free alternative for matching and populating lookup data locally. Enjoy high compatibility with Microsoft formats and a familiar interface without expensive subscriptions.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your datasets.
2. Use a lookup function: Select the empty cell where you need the matched data and type =VLOOKUP( or =XLOOKUP( to begin the formula.
3. Select your parameters: Highlight your lookup value, define the table array in your secondary data sheet, and press Enter to instantly populate the matching value.

Seamless compatibility with Microsoft Excel (.xlsx) and CSV formats.Easily match and populate dataset values using VLOOKUP, XLOOKUP, and INDEX/MATCH functions.Lightweight software that runs quickly even on older devices.Completely free to use with a highly familiar user interface, requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my flow fail when updating a lookup column?

Flows typically fail on lookup columns if you attempt to pass a text string instead of the referenced item's integer ID. Ensure you are mapping the 'ID' dynamic content from your source list to the lookup field in the update action.

Can I update multiple lookup values at once?

Yes, provided the lookup column is configured in SharePoint to allow multiple selections. To do this via Power Automate, you will need to construct and pass an array of ID objects to the update action.

How do I filter the 'Get items' action to prevent large data pulls?

You can use OData filter queries in the 'Get items' action settings to fetch only the items that match your specific criteria. This greatly improves flow performance and avoids processing unnecessary records.

Office Quick Solutions