Answered

Batch Lookup and Update Items SharePoint and Excel

(0) Share

Report

Posted on by sirdanman

We currently have a SharePoint list with some 60,000 records. Every 2 weeks, an Excel file is created that contains a about 1000-2000 records. I am trying to build a flow which would basically do similar to Vlookup functionality where it cycles through the Excel file, sees if the record exists in the SharePoint List, and then updates to the SP List to reflect the fact that this item has now been archived.

Is this possible in Power Automate? I've looked high and low and have found that vLookup functionalities in SP lists are not easy and that the size of the SP List also makes this task more difficult. Does anyone have any ideas? Thanks in advance.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Michael E. Gernaey 53,488 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Does the Excel have the Row Id, or a specific column Value to search against? I don't know exactly what you identify as a vlookup, but any column of data that matches will work. Having the row id however would be best since you could simply do an update, with an error handler, and not even do a lookup at all, since you have the rowid and you are sure it matches. Validating of course always makes sense though so, its up to you on if you are certain the data matches perfectly or not.

Of course you can do a list rows query etc.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
creativeopinion 10,502 Super User 2025 Season 2 on at

Like (0)

Report

@sirdanman To cross-reference items in an Excel table against items in a SharePoint list, you'll need to use a Filter Array action. I uploaded a YT Tutorial recently that covers how to use the Filter Array action. The first section of the video covers how to cross-reference an Excel table against items in a SharePoint list.

However with a SharePoint list that large, you'll most likely need a way to Filter down the items returned from your SP list using a Filter Query (if possible). Otherwise, you may need to return items in batches.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

Was this reply helpful? Yes No
sirdanman 20 on at

Like (0)

Report

@FLMike Yes, it does have an unique identifier which would serve as the basis of a relationship or lookup. What do you mean by "simply do an update?" Is there some kind of batch update or error handler built into SP or is this a specific type of flow? If so, can you point me to directions on how to build such a thing? Thanks.

Was this reply helpful? Yes No
sirdanman 20 on at

Like (0)

Report

@creativeopinion Yes, I actually watched some of that video already in looking for an answer to this, but I found it very confusing and not totally relevant to my situation. I was just hoping for an easier solution than creating that many custom variables and arrays.
And yes, I thought about that too. Thanks for the heads up about it though.

Was this reply helpful? Yes No
Verified answer

Michael E. Gernaey 53,488 Super User 2025 Season 2 on at

Like (0)

Report

Hello @sirdanman ,

If as you say they were a default "mapping" row then if I understand correctly what you want its not difficult.

Step 1: Make sure your Excel data is in a table. If not you will need to use Create Table Action, which will allow you to specify the Rows/Columns that make up your data. PA for Excel has to have a table.
Step 2: Do a List rows in a Table (Excel)
Step 3. Loop through the results of Step 2
Step 4a : If you have the ItemId of SharePoint item, then you can simply UPDATE it directly no need to do anything else. In Item ID (id) is required to do an Update, so you either have it in advance or you need to do Step 4b instead

Step4b : If you don't have the ItemId then you need to do a Get Items, using the Filter Query based on your matching column of data and setting Top Count to 1. Then loop through that Single record to get the ItemId, and then update.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
sirdanman 20 on at

Like (1)

Report

@FLMike This is helpful but I am confused about one part. What is the Filter Query supposed to be set to in the Get Items step in your model? I want to grab all of the items where the value of specific column matches the same value in the Excel file. Its a 1-to-1 relationship. So to me, the filter query would be [Excel Column Value] eq [SharePoint List Column Value], but under your model, I have no dynamic content to reference for the SP list. When I implant a Get Items step and reference that step's dynamic data, it just created a double Apply to Each loop, which seems like it wont work. See below.

EDIT: Nevermind, I figured it out. You don't use the dynamic data to fill out that query. You use the column name from the data source and use that to equal the Excel column name, basically. Thanks!

Was this reply helpful? Yes No