Excel Vlookup Using UiPAth

[anshul_nema]

Hi, I wold like to perform vlookup using UiPath, I have a excel file in that I have 2 Sheets (Sheet1 and Sheet2) in both the Sheet I have a common column (userId) with unique value, I want to do a lookup in Sheet one which tell me the common userId in both sheet, I have attached the screenshot of my excel file for reference.

How can we do this in UiPath.
Thank You!!!.

[arivu96 (Arivazhagan A)]

Hi @anshul_nema,
Refer this post,

Fastest way to write an excel formula into an entire column

Maybe of those approaches could help us to fulfill the entire column with the same formula, just replacing the row number from it. In this case, I would just write the formula at the first cell with Write Value activity and then apply one of those approaches following the link examples to replace the remaining rows: Using Send Hotkey activity to simulate the shortcuts that would help in this case. Using Mouse Click event (Double Clicking, more prone to errors) For example: [image] Never…

Regards,
Arivu

[MAHESH1 (MAHESHKUMAR JV)]

@anshul_nema

If you want to get common user ID’s You can try below Query

ListA=(From p In dt1.Select()
where (From q in dt2.Select() where p(“User ID”).ToString.Equals(q(“User ID”).ToString) Select q).ToArray.Count>0
Select p(“User ID”).ToString ).ToList

You will get the List of common user ID’s .
dt1-First excel sheet
dt2-Second excel sheet

Regards,
Mahesh

[arivu96 (Arivazhagan A)]

HI @anshul_nema,

refer this xaml file for VloopUp
Book1.xlsx (8.3 KB)
Vlookup.xaml (10.2 KB)

Regards,
Arivu

[anshul_nema]

Thank You Arivu… It’s working…

[sushmithaelluru (sushmitha)]

HI @arivu96, can you tell me what should be there in the “click and send hotkey activities” as it is showing image not available!!

Thanks.

[arivu96 (Arivazhagan A)]

Hi @sushmithaelluru,

While selecting the selector it will took the screenshot and its stord in the project folder.
. Screenshot → if you delete this folder activity shows image not available but it will not as per the logic.

Regards,
Arivu