Find o replace data table with multiple columns instead just one

[BLabat83]

Is there any way that using the function "Find or replace of a data table", I can do the search for two elements on two different columns?

For example, I have to search for the name of a person that would be in column 0 and also his address that would be in column 1.

Thank you eveyone!!

[Nived_Nambiar]

Hi @BLabat83 

Action Find or Replace in datatable will find the value in datatable everywhere or within a particular column and will return a datatable which looks like below 

Here Row column will contain the row index where it is located and column represent in which column of that row the value is located. In my case, I have used below datatable 

I am trying to find out the position of A1 in datatable which is at row index 0. So using the Find or Replace in datatable, you will get an output like above in datatable format where for each row you will get its row index in row column and column index in Column column, so using row index you can get which row value resides and based on which you can retrive the other column name 🙂

In my case, i need to retrive B column value where A1 row value is there, Try this way 

First convert the output from datatable macthes to number like below

Now use the below expression to get the B column value 

[BLabat83]

First of all, thank you very much for the quick response @Nived_Nambiar .

Thank you for explaining it and this part the truth is that I have already used it in other occasions and it works very well.

But in your example I need to look for the rows where is A1 and B1 for getting other information because I don’t have a primary key in the table.

any ideas? Thanks

[Nived_Nambiar · Accepted solution]

Hi @BLabat83 

Then i would suggest two ways 

1. Use for each and see whether the condition is acheieved or not 

2. Connect the excel as database and perform sql query to retrive data based on column value.

Refer here: https://www.matthewdevaney.com/power-automate-desktop-read-filter-excel-with-sql-query/

the second method is recommended method as it is faster than first one 🙂

Hope it helps !

[BLabat83]

Thanks @Nived_Nambiar . I also thought about the use of for each. Thank you for your help. I am going to leave this chat opened if someone has another idea.

thanks again!

[Agnius]

I would suggest doing the Find or replace in data table twice to find the two different values. Then use Retrieve data table column into list on the row index columns from both of the %DataTableMatches% tables. Then use Find common list items to compare the two lists with row indexes. This will return rows that contain both of your values.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[BLabat83]

This sounds very interesting @Agnius . Let me check and see if I can understand it.

Thank you very much!

[Agnius]

Let me know if it works and if this does solve your issue, please mark my answer as the solution. Thank you.

[BLabat83]

Thans @Agnius and @Nived_Nambiar . Finally I've used the For Each, easy to me. Thanks every one!