Lookup Datatable activity taking longer time to fill the excel column values

Hi Friends,

I have an **Excel file-1.xlsx (3.0 MB) ** to be filled. I have data in excel file-2. Excel file-2.xlsx (112.2 KB) I want to fetch the data from excel file-2 and fill it in excel-1 And I am using LookUp Datatable activity to fill the data(clientwise data).

The robot is able to fill the data but it is taking longer time which is more than an hour for just 5 clients

Now I want to fill the data for 60 clients… I don’t know how to proceed further.

In my code, I have used only one LookUp Datatable activity to fill the 5 clients data (which is around 459 rows).

My Excel file-1 & Excel file-2 are attached below for your reference.

Excel file-1 : I want to fill the column P in this file.

excel file-2 :
Column P values to be filled based on SALES PREMIUM column from the file.

Excel file-2 shown below for your reference

I have filled only 5 clients using single lookUp datatable activity. but taking longer time.

The five clients data to be filled are

3. JB
5. HCI(Home credit)

Excel file-2 has all the formulas, which I wanted to fill it into excel-1 columns
For eg., For Erafone, I want to fill it in column P(GMV Erafone) in my excel file-1

similarly, for XL-Era client, I want to fill GMV XL-Era which is Column AC

Please do the needful I want to do it for 60 clients. Please provide me some idea on how to fill for 60 clients using single lookup datatable activity in less time

@aanandsanraj @balupad14 @Palaniyappan


@eshwarsai_ks Can you also provide a sample output file for the Input files that you have provided, It will help us to understand the logic to be implemented in a faster way.

1 Like

Please find the sample output file attachedOUTPUT_.xlsx (1.1 MB)

Please note that column P (GMV Erafone) for the client Erafone is formula based values.

similarly, for other clients (XL-Era, JB,PPP(Indosat),& so on…), the columns to be filled was
GMV XL-Era(column AC)
GMV JB (column CC)
GMV PPP(Indosat) (column EB)

Hi @eshwarsai_ks,

I have created a logic to update formula for a client check out the xaml code
Main.xaml (11.0 KB)

1 Like

Thank you so much for your support @aanandsanraj. I would like to know one suggestion from you. Which one of the below points will give faster in order to fill values from one excel to another excel ?

1. Using Single Lookup Datatable to fill values in multiple columns
2. Using multiple look up Datatable activities to fill values in multiple columns

If I use LookUp DataTable for once to fill values for more than one columns, it is taking longer time which is 30min (only for 3 columns with 459 rows each)

Would it be a good option to fill data from one excel to another excel using LookUp Datatable activity??

May I know any other options do we have in UiPath to fetch data from one excel and fill other columns in 2nd excel file?

Lookup Datatable is giving accurate results but taking so long to fill (just tested for 5 clients, it took nearly 30minutes, i want to do it for 60 clients, so I can’t imagine how much time will it take )

Please suggest me on the same.

Thank you

I will check the workflow and get back to you asap @aanandsanraj


Instead of use lookup between the two datatable better use filter activity to find the specific value in the column. Then use for each loop to update the value it will be very fast and bot will complete the process in few seconds.

I created the xaml to update formula directly on the excel instead of table. If you want to update on the table just modify the code where formula is writing.

1 Like

Thank You @aanandsanraj I am going through the code. I will get back to you shortly :slight_smile:

Hi @aanandsanraj , It supposed to start filling data from the cell P5.instead it is filling from P4 and it is filling data from the wrong column

as there was no formula for this column in the source file

SOurce file : Excel-file-2

Also, It is taking 15 minutes to fill for just one column which is for ERAFONE.

I have 60 clients @aanandsanraj. In order to fill all the 60, it will take so much time na?

Any other possibilities other than this method?


Try this Main.xaml (13.5 KB)

1 Like

Thank you very much @aanandsanraj it worked in just 2:52 mins But I need to test it for other 59 clients as well. I will make use of the same logic to apply for all the clients and put it in a loop.

May I know whether can I use the same logic instead of LookUp Datatable?? Because I have other excel files to search for a data like above and then get the values and then need to fill the other columns. some columns are from one file and few of the columns from different excel files.

I’m thinking to make use of the same logic instead of using LookUp Datatable activity.

Any suggestions on this??


As I already mentioned use loop activity for these kind of logic. Lookup is very slow and use it in the correct scenario will help. So my suggestion would be loop activity.

One thing I have to tell you is I just updated only one column in the loop, you can update as many as column in that. So you can update all the clients value in the loop and it will not take much time. I hope it will be completed within 5 mins for all 60 clients.

1 Like

Thank you so much for your time and suggestions @aanandsanraj

I have another excel file like below where I am referring to column Row Labels and getting the values of “SUM OF AMS MARGIN”, “SUM OF UW COST” & SUM OF QTY

I want to fill the above values in Excel-File - 1.xlsx by taking column Type as my unique value and fill the above 3 columns values in my ExcelFile-1 in columnD, Col F and Col H.

Excel file-1.xlsx :

Can I use the same logic?? If yes, Where do I need changes. PLease support on the same.

Thank you


Yes you can use the same logic. Just go through the code and try to understand the same. In the for each loop you have to modify the code for your new excel.

1 Like

Thank you. @aanandsanraj will do it :+1: