Get column value from List 1, search it in List 2, if a match is found, compare two different columns

[AlexandruChiser]

Hello All,

I have two list: one with Users, and one with Departments.

The first one has the following columns: User / Email / Department / Group
The second one has the following columns: Department / BU

In the first list, sometimes there is a discrepancy between the Department and the Group and I want to fix this, like so:

Go through each of the items in the USERS_LIST, get the Department value, search for it in the DEPARTMENTS_LIST (it contains only unique values), if a match is found, compare the DEPARTMENTS_LIST.BU.value with USERS_LIST.GROUP.value ... if those are not equal, return the DEPARTMENTS_LIST.BU.value

I've tried: Get Items for USERS_LIST > Apply to each of these items > Get Items from DEPARTMENTS_LIST using filter Department eq 'USER_LIST.Group.value' > Condition: USERS_LIST.Group.Value is equal to DEPARTMENTS_LIST.BU.value > send email with the value

But it doesn't work...

Any idea on how do achieve this?

Thanks!

[AlexandruChiser]

I've done this so far, but it takes 30+ minutes to run for 2000 entries...

At then I just send an email with the string variable... 

[Matthy79]

If you provide sample data (JSON and not Screenshots) and a desired output, I will create a solution.

[creativeopinion]

@AlexandruChiser 

The logic of your flow needs to be adjusted. Using and Apply to Each and condition action to run a check for each item (especially if you have a large list is very inefficient). Also, best practice is to run a Get Items action outside of an Apply to Each action and use a Filter Array action instead. You should only run a Get Items action once for each list/library in your flow—rather than running it multiple times.  

To learn more about how to use the Filter Array action, please refer to this recent YT Tutorial I uploaded.

To further improve the efficiency of your flow—you'll want to try to avoid setting variables unless you absolutely need them. In your case—you don't need them. The concurrency control in the Apply to Each action can be toggled on when you aren't setting any variables inside the action. If you are, you would have to leave this off.

You can turn on the concurrency control for any of your Apply to Each actions in your flow—as long as you aren't setting a variable (otherwise this can cause unexpected results). The concurrency control will run the Apply to Each action concurrently (same time—up to 50 instances at once). 

A couple of things for you to consider:

• How often do you plan on running this automation? Do you want it to run on a schedule?
• After the automation has run—what are the chances the information in the Users list would change again? Would someone manually go in and edit it after you've run the automation?
• Can you mark items that have been checked and edited so that you don't need to run the automation on those items again? This would reduce the amount of time it takes your flow to run as you can specify items that have not been edited via the automation so that you can leave those items (already changed) out the next time you run the automation again.

---

Tip: Rename your action to keep things organized. Use Scope actions to group actions together so you can quickly collapse multiple actions with a single click. Run tests at each stage of your flow to confirm the outputs. 

Flow Logic

Since you want to look up the Department List and run a cross-check against the Users list you'll want to do the following:

• Get Users List – Ideally you'd be filtering out this list as mentioned above
• Get Dept List – Not necessary to filter unless there are inactive departments (in which case you should filter those out.
• Loop through each department
  • Filter out users by current dept being looped through and cross-checking the BU value (in my demo case below it'll be the Dept Role) with a Filter Array action. Rather than checking each user individually.
  • If there isn't a match — update the item.
    With this flow logic you are streamlining your flow by looping through the depts and running a cross-check on multiple items at once with the Filter Array action. Only if there isn't a match, would you run an update on an item.

You'll need to use the Classic editor to follow along.

Get Users List

Add a Get Items action to return the list of users. Tip: Depending on how large your list is, I'd recommend limiting the number of items returned to a small number while you are building and testing your flow. This will help to speed up your flow run. 

Get Dept List

Add a Get Items action to return the list of departments.

Select User Fields

Add a Select action to your flow. The Select action allows you to choose specific fields from the data retrieved—in this case from the Get Items (User List) action.

Important: Since you have TWO Get Items actions in your flow you'll need to ensure you are selecting the dynamic content from the right action. This is why it's important to name your actions.

In this case, you only need to return the following fields:

• ID
• Dept
• BU (Department Role in my Case)
• Title (this is only necessary if your title field is a required field in your list—if not you can omit this)

In the from field, insert the value dynamic content from the Get Items action (which returns items from the User list).

Enter a key in the left column and insert the dynamic content from the Get Items action in the right column. Repeat this for each dynamic content you'd like to return from the User list.

If you are returning values from a choice column you'll need to ensure you are selecting the value dynamic content for the choice column.

Run a test. Verify the outputs from the Select action.

Loop through Each Department

Add an Apply to Each action. Insert the value dynamic content from the Get Items action that is returning the list of Departments. Don't forget to turn on the concurrency control!

Add a Compose action to confirm the current dept being looped through. This step is optional however I find that it can be helpful especially with complex flows—it'll help you better understand what's going on as well as identify any issues with your flow early on.

Filter User Items

Add a Filter Array action to your flow—ensure you've nested this inside the Apply to Each action. You'll use this action to filter the items returned from your User list by the current department being looped through. 

In the From field, insert the Output from the Select action.

In the first value field, you'll need to insert an expression to access the dynamic content of the department from within the Select action. You'll need to use the following expression:

item()?['']

In between the single quotes you'll need to insert the dynamic content key. Remember, you set this key in the Select action—it's Dept in my case (it might be different in your case depending on the key you entered).

Enter the key between the single quotes.

Keep the operator as is equal to. In the second value field, insert the output from the Compose action above (which contains the current dept being looped through.

Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the body dynamic content from the Filter Array action into the length() function.

Run a test. Review the output of the Compose action. The number displayed should reflect the number of Users assigned to the current dept.

Add Another Condition to Filter Array

Next you'll need to adjust the Filter Array action so it only pulls the users where the Dept Group doesn't match the current Dept BU (in my case it's the Dept Role). Although it may look like the Filter Array action can only take a single condition—it can take multiple conditions when using the Advanced Editing Mode. However, you'll need to compose the expression in a text editor as it's impossible to use the advanced editor in Power Automate.

Reference this section of this YT tutorial for how to add multiple conditions to a Filter Array action.

In the Filter Array action, click on Edit in advanced mode.

Highlight the expression and copy it to your clipboard.

Paste it into a text editor

Select Edit in basic mode. You'll be composing the next condition. Remove the dynamic content from the first value field.

Insert an expression into the first value field. You need to grab the dynamic content from the Select action that is storing the Dept BU (Dept Role in my case). Reference the key that you've entered in the Select action. In my case, it's DeptRole. 

I'll use the item() function again and enter DeptRole in between the single quotes.

item()?['DeptRole']

Change the operator to is not equal to.

Clear the dynamic content from the second value field. 

Insert the output from the Compose action that is storing the Dept BU value (in my case, it's the Department Role Value).

Click on Edit in advanced mode and copy the expression to your clipboard. Paste it onto the second line of the text editor.

First add a comma to separate both expressions.

Then delete the @ symbol from the second line. Next wrap the entire expression with an and() function. Place your cursor after the first @ symbol and type in and with an opening parenthesis. Go to the end of the expression and add a closing parenthesis.

Copy this entire expression to your clipboard and paste it into the Filter Array action (ensure you replace what is there already).

Run a test. Verify the output of the Compose action storing the count of items from the Filter Array action. The number you are seeing should reflect only those items that need to be changed.

Condition Check

Add a Condition action. You'll use this action to check if there are any items that need to be changed before continuing on with the flow. This helps to streamline your flow as you will only run the Update Item action if there are items to update.

In the first value field, insert the output from the Compose action above. Change the operator to is not equal to and enter zero into the second value field.

Loop through Each Employee Item

Add an Apply to Each action into the YES branch of the condition action. Add the Body output from the Filter Array action looping through the User list (in my case it's the Filter Array - Employee Current Dept action). Don't forget to turn on the concurrency control!

The next step is optional—however I strongly recommend it. If you are new to Power Automate, it's always a good idea to confirm any dynamic content you'll be using in actions that Update or Create items before you run those actions.

In this case, I'll be using two compose actions to store the ID and Title dynamic content. Since you are using the Apply to Each action to loop through the body of the Filter Array action, you will not be able to select dynamic content from the list—you'll need to use an expression instead.

Refer to this section of a YT Tutorial I uploaded on how to get dynamic content from a Filter Array action

You'll need to use the item() function and insert ID and Title into the single quote marks.

Run a test. Verify the output. Remember that the Compose action will only have an output if the Condition is true.

Update Item

Lastly, you'll need to add an Update Item action to your flow. Ensure you are adding it inside the Apply to Each action. 

In the ID field, insert the output from the appropriate Compose action above.

In the Title field, insert the output from the appropriate Compose action above.

Locate the dropdown for the Department BU Value (in my case it's Department Role Value). Select Enter custom value from the dropdown.

Insert the output from the Compose action storing the Department BU value (in my case it's the Department Role Value).

Run a test.

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

If you'd like to level up your Power Automate skills check out this video: Power Automate Beginner Tips and Tricks | 5 Things You Need to Know – Part 1

I cover the following in this video tutorial:

✅ Power Automate Beginner Tips and Tricks

✅ Why use the Manual Trigger instead of an Automated Trigger

✅ 3 Ways a Compose Action can help you build better flows

✅ How to Manually Trigger a flow with a specific Date and Time

✅ How to return a count of items

✅ How to use the top count

✅ How to send test emails