Others

How to Use Power Automate to Match Data Between SharePoint Lists

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to create a Power Automate flow that acts like an Excel VLOOKUP, matching records in one SharePoint list with another and updating the destination list based on common fields.

Product: Power Automate, SharePoint
Device & OS: not provided
Scenario: Automating data synchronization and lookup across multiple SharePoint lists when a new item is created.
Observed behavior: The goal is to automatically update a newly created TargetList item with specific values pulled from a SourceList by matching a category field.

Before you start

Ensure you have active connections to both SharePoint lists in your Power Automate environment and verify that both lists share a common identifier column (like a category ID) for accurate matching.

Solution 1Recommended

Create a VLOOKUP-Style Flow in Power Automate

Build an automated cloud flow that triggers when a new item is created, retrieves source data, checks for matching fields, and updates the target item.

By using the 'Get items' action combined with an 'Apply to each' loop and a condition, Power Automate can replicate the behavior of Excel's VLOOKUP function directly within SharePoint.

For better performance on large lists, you can utilize an OData Filter Query within the 'Get items' action. This filters the data at the server level, returning only the matching records instead of fetching all items into the flow.

Create the trigger

In Power Automate, create a new automated cloud flow. Set the trigger to "When an item is created" and select your target SharePoint site and list (TargetList).

Retrieve source list data

Add a new step and search for the "Get items" SharePoint action. Configure it to connect to your source SharePoint list (SourceList).

Add an Apply to Each loop

Add an "Apply to each" control. Click in the input box and use the dynamic content "value" output from the "Get items" step to loop through the retrieved records.

Set up the matching condition

Inside the loop, add a "Condition" control. Set the rule so that the TargetCategory (from the trigger) is equal to the SourceCategory (from the Get items action).

Update the target list

In the "If yes" branch of the condition, add an "Update item" action. Select your TargetList, provide the Item ID from the trigger step, and populate the necessary fields using dynamic content from the matched source record.

Community Support: If you require highly specialized OData queries or encounter complex matching scenarios, the Power Apps Community Forum provides extensive resources and customized assistance.

Free Microsoft Office alternative

Switch to WPS Office for Effortless Spreadsheet Data Management

While Power Automate is excellent for SharePoint infrastructure, managing large datasets, comparing data, and executing complex lookups is often much faster and more straightforward in a dedicated spreadsheet application. WPS Spreadsheet offers powerful data analysis tools and robust built-in VLOOKUP functions entirely for free.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your Windows, Mac, or Linux operating system.
2. Open your data in WPS Spreadsheet: Export your SharePoint list to Excel format or open any existing .xlsx file directly in WPS Spreadsheet.
3. Use native VLOOKUP: Select an empty cell, type =VLOOKUP(, and select your lookup value, table array, column index number, and range lookup to easily match data between sheets instantly.

Seamlessly execute VLOOKUP, XLOOKUP, and complex formulas to match data across tables without building automated flows.Fully compatible with Microsoft Excel (.xlsx, .xls) files, ensuring no formatting is lost.Lightweight software that runs quickly on any device, reducing load times for large datasets.A familiar user interface makes transitioning from MS Office quick and effortless.

microsoft office alternative - wps office

Frequently Asked Questions

How can I optimize my Power Automate flow for large SharePoint lists?

To prevent the flow from retrieving thousands of items and slowing down, use an OData Filter Query in the "Get items" action. For example, set the query to `Category eq '@{triggerOutputs()?['body/Category']}'` to fetch only the exact matching record.

Will this flow work if multiple records match the condition?

Yes, the "Apply to each" loop will process all matching records. However, if multiple source records match, the "Update item" action will overwrite the target item multiple times, leaving it populated with the data from the final processed match.

Do I need a premium Power Automate license to connect SharePoint lists?

No, the standard SharePoint connector in Power Automate is included with most Microsoft 365 plans and does not require a premium license for basic triggers and actions like "Get items" and "Update item".

Can I use this method to update an Excel file instead of a SharePoint list?

Yes, you can replace the SharePoint "Update item" action with the Excel Online "Update a row" action. Ensure your Excel file is saved in OneDrive or SharePoint and contains a formatted table with a unique Key Column for the action to work correctly.

Office Quick Solutions