Others

How to Populate Microsoft List Lookup Columns from Microsoft Forms

Khadija Khan Sep 28, 2026 869 views

Question details

The user needs to populate a SharePoint or Microsoft List lookup column using data submitted through Microsoft Forms via Power Automate.

How to Populate Microsoft List Lookup Columns from Microsoft Forms

Product: Microsoft Lists, SharePoint, Power Automate
Device & OS: not provided
Scenario: Automating data transfer between Microsoft Forms and a SharePoint/Microsoft List where a lookup column is involved.
Observed behavior: A SharePoint lookup column requires an item ID rather than plain text, requiring a workaround in Power Automate to match the form response string to the correct list item ID before creating or updating the destination list record.

Before you start

Ensure you have adequate permissions to edit the Power Automate flow, access the Microsoft Form, and read/write to both the target list and the source list used for the lookup.

Solution 1Recommended

Retrieve and Map the Lookup Item ID using Power Automate

Use the 'Get items' action in Power Automate to filter the source list by the form response text, retrieving the required internal ID for the lookup column.

SharePoint lookup columns cannot directly accept plain text values submitted via Microsoft Forms. They require the internal integer ID of the matching item from the source lookup list. By utilizing the 'Get items' action, you can dynamically search the source list and retrieve this ID during the workflow execution.

Trigger the Flow

Create an automated cloud flow with the trigger 'When a new response is submitted'. Add the 'Get response details' action to fetch the data entered into the Microsoft Form.

Add the 'Get items' Action

Add a new action and search for 'Get items' (SharePoint). Set the Site Address and List Name to the source list that your lookup column pulls its options from.

Filter the Source List

In the 'Get items' action, click on 'Show advanced options' and configure the 'Filter Query' field. Map the list's display column (e.g., Title) to the dynamic content from your form response using OData syntax (e.g., Title eq '[Form Response Dynamic Content]').

Map the ID to the Destination List

Add a 'Create item' or 'Update item' action for your destination list. Click on the lookup column field, select 'Enter custom value', and insert the dynamic 'ID' from the 'Get items' action.

Allow the 'Apply to each' Loop

Because 'Get items' returns an array of results, Power Automate will automatically wrap your 'Create item' action in an 'Apply to each' loop. This is expected behavior and will function correctly assuming your lookup values are unique.

Retrieve and Map the Lookup Item ID using Power Automate

Handle Empty Results Safely: If a user submits a value that does not exist in the source lookup list, the 'Get items' array will be empty and the loop will be skipped. Consider adding a 'Condition' action to verify the array length before attempting to create the item.

Free Microsoft Office alternative

Streamline Your Document Workflows with WPS Office

While Power Automate and SharePoint are excellent for complex automated backend lists, WPS Office offers a free, lightweight, and highly intuitive alternative for your everyday document, spreadsheet, and form collection needs. With excellent format compatibility, it simplifies data handling without complex scripting.

1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite and follow the simple installation prompts for your operating system.
2. Create a New Form or Spreadsheet: Open WPS Office and select 'Forms' to gather data quickly, or open 'Spreadsheets' to manage your list data efficiently.
3. Export and Share Data: Easily export your collected data to standard .xlsx format to share with colleagues or analyze using built-in pivot tables and functions.

Built-in WPS Forms for quick and easy survey data collection.Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) file formats.Lightweight application with fast launch speeds and offline capabilities.Familiar, tabbed user interface ensuring zero learning curve for Microsoft Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Can I map plain text directly to a SharePoint lookup column?

No. SharePoint lookup columns inherently store the unique integer ID of the reference item, not the plain text value. You must always pass the ID into the column field via Power Automate.

Why does Power Automate add an 'Apply to each' loop when I select the dynamic ID?

The 'Get items' action is designed to return a list (array) of items, even if your filter query results in only a single match. Because it is an array, Power Automate automatically adds an 'Apply to each' loop to iterate through potential multiple values.

What happens if multiple items match my form response filter query?

If your source list contains duplicate display names, the 'Get items' action will return all of them. The 'Apply to each' loop will then execute multiple times, creating or updating multiple entries in your destination list. It is crucial to ensure lookup display values are unique.

How do I fix a flow that fails when the lookup item isn't found?

You can prevent errors by adding a 'Condition' action right after 'Get items'. Use the expression 'length(outputs('Get_items')?['body/value'])' and set it to 'is greater than 0'. Only proceed to create the item in the 'If yes' branch.

Office Quick Solutions