vlookup equivalent for power automate

(2) Share

Report

Posted on by Sormick

566

Hello,

I'm trying to get values of an Excel table from at least three columns, filter these columns based on these conditions below using a "Filter array" Element

and(
not(endswith(item()?['Item ID'],'FR')),
equals(item()['EMD event'],''),
equals(item()['Destination country'],''),
equals(item()['EMI event'],''))

After using the "Filter array" Element i used the "Select" Element to get all the Filtered values of the Excel column named ['Item ID']

item()?['Item ID']

and finally i send all the output values of my "Select" element on a sharepoint list.

My issue is i'm trying also to get the values from 2 other excel columns with the same filter conditions above but did not managed to to that.

Here is my current flow below:

Categories:

Building flows

I have the same question (0)

All responses (33)

Answers (1)

Sort by

Michael E. Gernaey 53,978 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Sormick

Please share your flow, so we can see the inputs/expressions for the design, and please share the flow run where you had the issue, showing the Inputs and Outputs of the flow.

You can do this by clicking the flows details page and clicking the Run that had the issue you need fixing.

Is this an error or it just didn't do what you wanted? The only reason for it to not work, is if the Tables don't have the same column names, or value types or its pointing to the wrong file etc

I see the Get Tables and then list rows, but I only see 1 loop, so please share your entire flow.

Was this reply helpful? Yes No
creativeopinion 10,508 Moderator on at

Like (0)

Report
Copy link

Link copied!

@Sormick You might be interested in these YT Tutorials:

Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.
1️⃣ Cross-Referencing Data
2️⃣ Filtering by Key
3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:
✓ 3 Ways to Use the Filter Array Action
✓ How to use the Scope Action to Group Actions
✓ How to Check the Number of Items returned from a Filter Array Action
✓ How to Cross-Reference Data in Excel with a SharePoint List
✓ How the Filter Array Action Works
✓ How to Access the Dynamic Content from a Filter Array Action
✓ How to Filter Items by a Key
✓ How to Filter Items by Matching a Substring
✓ How to Use Multiple Conditions in a Filter Array Action

---

Filter Array + Apply to Each: The Best Tip You Need to Know

In this tutorial—I’m going to show you a quicker way to get the dynamic content from your Filter Array action—and it doesn’t require writing an expression.

IN THIS VIDEO:
✓ How to Loop Through Filter Array Results in Power Automate
✓ Using Apply to Each with Filtered Arrays
✓ The Easiest Way to Access Dynamic Content from Filter Array
✓ Fixing Nested Apply to Each Actions
✓ When to Use Value vs. Body Dynamic Content
✓ Simplifying Power Automate Flows with Filter Array
✓ Troubleshooting Filter Array and Apply to Each Issues

---

3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:
1️⃣ Looping through a Single Item
2️⃣ Creating Unnecessary Nested Loops
3️⃣ Looping through an Unfiltered Array

At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

IN THIS VIDEO:
✓ How to avoid the Apply to Each action with a single item array
✓ How to use the item() function to access dynamic content in an array
✓ How to prevent unnecessary nested Apply to Each action loops
✓ How to use the Select action
✓ How to convert an array to a string with the Select action
✓ How to use the Filter Query field
✓ How to count the number of items in an array
✓ How to use a condition control
✓ How to use the concurrency control
✓ How to set a top count
✓ How to use Compose actions for troubleshooting

Hope this helps!

Consider giving me a ❤️ if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

Was this reply helpful? Yes No
eliotcole 4,390 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi, I would suggest that you give us some example data (not the real data) and what you want to get out of it.

However, my initial advice would be to run multiple filters, then combine the results however you need to. :-)

Was this reply helpful? Yes No
Suggested answer

ronaldwalcott 3,866 Moderator on at

Like (0)

Report
Copy link

Link copied!

You said that you are using the same filter therefore the other columns are available at the same time that Item ID is and can be extracted. Unless you mean that there is another filter condition that you want to use.

See the example here 5 Surprising Power Automate Select Action Tricks

Was this reply helpful? Yes No
w.p 8,345 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Sormick

It seems possible to archive by mapping values, but I want to see more sample data first before offering a solution.

Was this reply helpful? Yes No
Sormick 566 on at

Like (0)

Report
Copy link

Link copied!
Thank you all for your replies. However still did not managed to send all filtered columns to my sharepoint list.

Please find here below a screnshot of my Excel Columns and values i need to filter:

What i need to do is to get the filtered values from columns named "Item ID" "receptacle ID" and "Weight" and send the results to a sharepoint list on separated columns corresponding to the current ones here on my screenshot.

The filter conditions for all the columns are:

on column "Item ID" " all the values not ending with "FR"

with additional filter to columns named "EMD event" and "EMI event" which must contain no dates (empty cells)

with addtional filter to column named " "Destination Country" which must not contain any values (Empty cells)

If i put this filter contidions to the "Filter array" element of my flow below and next add a "Select" Element with [{item()?['Item ID']}

i can get all the filtered values of the column "Item ID" but i need also tto get the filered values of these columns also "receptacle ID" and "Weight" and now i have difficulties to do that

and( not(endswith(item()?['Item ID'],'FR')), equals(item()['EMD event'],''), equals(item()['Destination country'],''), equals(item()['EMI event'],''))

Was this reply helpful? Yes No
ronaldwalcott 3,866 Moderator on at

Like (0)

Report
Copy link

Link copied!

Where are you selecting the other columns in your flow?

Was this reply helpful? Yes No
w.p 8,345 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Sormick

I don't understand this last part and no 'Destination country' column, may be in another spreadsheet.

but i need also tto get the filered values of these columns also "receptacle ID" and "Weight" and now i have difficulties to do that

Was this reply helpful? Yes No
Sormick 566 on at

Like (0)

Report
Copy link

Link copied!

Dear @w.p,

I'm sorry i deleted some Excel columns before making a screenshot and deleted acidentally the column "Destination country"

Was this reply helpful? Yes No
ronaldwalcott 3,866 Moderator on at

Like (0)

Report
Copy link

Link copied!

You are now showing that you have two distinct tables.

Are you trying to get values from one table or two tables?

If it is both tables then you have to access both tables to select the values

Was this reply helpful? Yes No