Lookup data table or Excel in Power Automate Desktop

Like (0) Share

Report

Posted on 1 Nov 2022 15:16:10 by Mels_robots

Hi,

Can someone help me find out how to look up multiple values in a excel sheet.

It should find a specific value in column 3 and then return the value in column 2 from the same row in Excel.

When the value (in this case name) only appears once it seems pretty clear. I used the Find and replace cells in worksheet. And then read from excel using the found row and found column.

However when the name appears multiple times in the worksheet I got a problem.

Instead of the found rows and columns I get a datatable %Cells%.

Can someone help me on how I can use the data to find the matching rows in a loop for each name?

All responses (3)

Answers (1)

Verified answer

Mels_robots 16 on 03 Nov 2022 at 13:21:37

Like (1)

Report

Re: Lookup data table or Excel in Power Automate Desktop

@VJR sorry I’ve found my bug.
I used %Cells% instead of %Current_Cell% from the loop in the Read From Excel StartRow. 🥴🤦🏻‍
Finally got I right 🙂

thanks again.
Mels_robots 16 on 03 Nov 2022 at 12:11:55

Like (0)

Report

Re: Lookup data table or Excel in Power Automate Desktop

Hi @VJR ,
thanks for your reply.
I’ve tried this but can’t get it to use the data table %Cells% is my syntax wrong? I’ve tried both with and without the ‘ in %Cells[RowIndex]%
VJR 7,635 on 02 Nov 2022 at 02:59:13

Like (1)

Report

Re: Lookup data table or Excel in Power Automate Desktop

Hi @Mels_robots

If you keep the All Matches enabled, then it will give you the Cells datatable.

If disabled, then it only gives the Found Row and Column.

If you are going to have multiple matches then use All Matches.

Whether you have a single match or multiple the below loop will read them.

- Add a For each loop

- Use the Cells Datatable for the Loop

You can make use of the %Cells.RowsCount% property if you want to know whether there is a single match or multiple or no match at all.