Vlookup column from one excel file into another

Learn Academy Feedback
1

Hey guys,

Can someone create process for Vlookup two excel files attached and send me .xaml file?
Files excel.7z (171.1 KB)
I have been struggling with this issue several days and still it is not working.
Column from second excel file should be Vlookuped to first like this:
File1_File2
Vlookup_result

2

@Apple1

Check as below for your reference
Tutorial : How to check a string is present in Excel Sheet || Part - 1

Hope this helps you

3

Hi @Srini84,

I created similar process, but I need to write cell values from File2.xlsx into the new column of FIle1.xlsx taking only those rows that exists in both file. How can I do it? I saw a lot of tutorials in web but could not find it.

4

Hi @Apple1

Kindly follow this way

  1. use read range to read the File2.xlsx file and store in dt1

  2. use read range to read the File1.xlsx file and store in dt2

  3. use for each row for looping through each row of dt1, Here in for each row property panel there is field called index, it will store the index of row of datatable through which u are looping. in that create a variable which store the index of iterating row , lets say rowindex

  4. inside for each row use lookup datatable activitiy with following parameter in the property ,

    a) lookup datatable = dt2
    b) lookupvalue = row(0).ToString
    b) lookup column index = 0
    c) rowindex = index ( an int32 varible which stores the rowindex of that lookupvalue in the lookup datatable

then after lookup datatable use an if Condition, with condition as,
index = -1, if index equal -1 then it indicates that there is no value in the dt2 which we are searching , so in then section of if activitiy , do nothing

While if index is not equal to -1 then the lookup value is there in dt2, then in else condition we need to use write cell activitiy with path of File1.xlsx and cell as “B”+(rowindex+2).ToString ,

with cell value as row(0).ToString

Like this way try and let me know

Mark it as solution if u got it

Nived N :robot:

Happy Automation :relaxed: :relaxed:

5

Hi @NIVED_NAMBIAR
Thank you very much for you responce!
I created process as you described and it writes result in Excel, but only first found item from File2.xlsx to File1.xlsx in the range of cells:

Vlookup1
Vlookup1292×701 5.24 KB

The screen of result in Excel file above shows that found matched item: B0047 written only to “B10” cell, however it should be written into the range of “B11:B20”. Also found matched item B0066 is written into the cell “B21” only, however it also should be written in the range of cells “B22:B34”, can you please help how to do it?

6

Hi @Apple1,
I think instead of index to write the cell value, u need to use index value of row through which it need to iterating,
Kindly check my above post i had updated it.

Hope it helps

Nived N
Happy Automation

7

@NIVED_NAMBIAR
I changed Write cell Activity range to “B”+(rowindex+2).ToString and it is showing an error: “rowindex” is not declared. It may be inaccessible due to its protection level.
Where can I declare rowindex?

8

Hi @Apple1

image
image1920×1080 210 KB

In the property panel there is output section where there is Index, in that index u need to define the rowindex variable

Regards
Nived N

9

I updated the process and it is giving wrong result after 15 minutes of process execution:

LookupWrong
LookupWrong232×825 7.25 KB

Can you please check where could be an error?

10

YES , can u share the excel file?

1 Like
11

I attached them in .7.z
Files excel.7z (171.1 KB)
Are you able to open it?

12

yes @Apple1, will check and let u know

1 Like
13

:+1:
waiting for your reply

14

Hi @Apple1
is it u need finally?

image
image1920×1080 216 KB

1 Like
15

Hi @NIVED_NAMBIAR
Yes exactly :+1:

16

Hi @Apple1
kindly find the attached workflow for reference:
Lookup_Case.zip (366.7 KB)

Mark it as solution if u got it
Regards
Nived N
Happy Automation

1 Like
17

Thank you @NIVED_NAMBIAR
It works!
Process takes 40 minutes for execution on my side. What time did it take on your side?
Are there ways to make it work faster?

18

I think u need to use linq query for this

19

@NIVED_NAMBIAR can you please create process with linq query as well?

20

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.