Run excel VLOOKUP in uipath

Help
1

How do I run vlookup on a workbook using values from two other workbooks,

I am trying to fetch phone numbers using matching names but the phone numbers are in more than one workbook?

what to do?

2

Hi @JoshuaOke

Use write cell to pass the value

Thanks
ashwin.SVlookup.xaml (6.3 KB)

3

Hi @JoshuaOke,

You can use the Run Macro activity or Invoke VBA to run a macro, in a macro/vba you can have any excel formula or anything related with excel. That’s one solution, another one could be to download an Uipath package that does a vlookup but honestly, I prefer the first one. Good Luck!

4

First step if you are not comfortable with macros as @rmunro has suggested is to maybe get the data out of excel. Simply read the various files into datatables.

Then you can use the Lookup DataTable activity. Much less painful than trying to get UiPath to start writing excel formulas into your workbooks.

1 Like
5

@JoshuaOke

  1. Try to get the vlookup formula once manually
  2. Then store it in a variable (after changing if you want to)
  3. Use write cell activity to get the value for the first time
  4. use Auto Fill Range to get all the values from another excel or upto the existing sheet range
3 Likes
6

Hi,
May I know if the above steps could be performed in workbook .
I am trying to avoid excel application scope for retaining cell format

7

Yes, we have write cell activity available with the system.file as well

image

8

Hi,
I have a following requirement where excel-1 will have Name and excel-2 will have Name,Designation and package. as follows:
image and image .
i want to use vlookup to search in excel-2 to find the vpackage value of peter with senior software engineer as designation. basically I want to use vlookup for filtering the excel -2 based on two column.

Thanks.
Dasharath ND

9

Read both tables into datatable objects then you can complete using the lookup datatable activity

10

but i want to validate both the columns in excel-2.both Name,Designation column needs to satisfy.

please note i have designation column in excel-1 also.

11
  1. Read both tables into datatable objects.
  2. Place dt1 in in a ForEach Row loop.
  3. In the For Each perform a lookup up activity twice. One targeting the Designation column in dt2, the other targeting the Name column. Then, in an IF condition, if both the returned values equal their equivalent in dt1, grab the Package.

IF dt2value_name = row.item(“Name”).tostring and dt2value_designation = row.item(“Designation”).tostring THEN

Lookup Datatable, target the Package column

12

can you provide any sample xaml file??

13

Lookup datatable example