Lookup data table or Excel in Power Automate Desktop

For a long time, one of the Achilles heels for Power automate desktop was the lack of lookup functionality in Power Automate Desktop. The only way to find out if a certain value exists in an excel or data table was to loop through the entire data table.

With the recent August update, Power automate desktop has added a nifty new connector called as “Find and replace cells in Excel worksheet”.

Let us see how this new connector works in Action:

I have an excel with the following Data.

Excel Data

Now let’s look at how we can use the new “Find and replace cells in Excel worksheet” connector with our excel data

Problem 1- Getting the first instance of “Randy”

As you can see from the below screen grab we have set Search mode to Find, turned off All matches and enabled Match entire cell contents. Match entire cell contents makes sure to find only the cells that has the word “Randy”.

This connector outputs two variables “FoundColumnIndex” and “FoundRowIndex”.

Searching for First instance of Randy

On running the automation, the variables “FoundColumnIndex” and “FoundRowIndex” have values 1 and 6. This matches the row number and column number in Excel.

Index values for “Randy”

Using Row Index for lookup

Now we can use the value of FoundRowIndex as look up for the values in corresponding columns. To find the age of Randy, we use the value from variable FoundRowIndex as Start row and use column number of “Age” column which in this case is 2.

Using Row Index for looking up corresponding columns

Now let’s look at the output of variable Age after running the automation. It shows the Age as 15 which is as correct. This way we can use this new connector as lookup for excel or data table :)

Problem 2- Getting all instances of “Randy”

If we would like to get all instances of a particular word then we need to turn on the option “All Matches”. This returns a data table consisting of rows and columns for the matching word.

All Matches of the word Randy in Excel

Now let’s take a look at the output of the above automation step. We get data table with ColumnIndex as 1 and RowIndex as 6 and 8. This is exactly the same column and row numbers in the excel

Match for Randy is found in rows 6 and 8

Problem 3- Getting all instances that contain the word “Randy”.

I have added a new row with name “Brandy” to the excel workbook.

Now if we want to get rows that contain the word “Randy” , we need to disable or switch off the option “Match entire cell content”

Find all cells that contain the word Randy

Let’s look at the output, once we run this automation. We can see that row 9 is also captured in Cells data table in addition to the rows 6 and 8

I hope you guys found this post useful.