VLOOKUP USING studioX

Help StudioX
1

How to vlookup between two excel sheets with unique column reportid, using studiox UiPath. Can someone give an idea about it I tried on multiple ways

2

Hi @govindreddy_nagireddy

Please check below thread, If possible share your sample input excel and output

Regards,

3

Uploading: file1.jpg…
[Uploading: file2
Uploading: destination.jpg…
.jpg…]() after applying lookup I need to get as destination file

I need all the information like the rows but when comparing with reportid here is unique

4

@govindreddy_nagireddy

Files not loaded properly please send it again

Regards,

5

[Uploading: file1
Uploading: file2.jpg…
.jpg…]()
Uploading: file2.jpg… after applying vlookup file2 looks must look like this

6

IMG_4920
IMG_49202268×4032 2.84 MB

IMG_4921
IMG_49214032×2268 2.95 MB

IMG_4922
IMG_49224032×2268 2.3 MB

8

1st image indicate one excel file file1
2nd image indicate 2nd file2
In file2 I need to get the non matching rows like in NA in second image

9

@govindreddy_nagireddy

Try this

This image depicts a workflow for automating Excel tasks, including assigning a file name, using an existing Excel file, writing a VLOOKUP formula to a specific cell, and applying autofill in an Excel sheet. (Captioned by AI)
This image depicts a workflow for automating Excel tasks, including assigning a file name, using an existing Excel file, writing a VLOOKUP formula to a specific cell, and applying autofill in an Excel sheet. (Captioned by AI)818×1124 40.5 KB

Input:

Excel1:

The image shows an Excel spreadsheet with column headers "Reportid," "class," and "mail," listing data entries associated with report IDs 1212 and 1313. (Captioned by AI)

Excel2:

A spreadsheet with columns labeled "Reportid," "class," "mail," and "CD," containing four rows of data. (Captioned by AI)

Output:

Excel2:

The image shows a spreadsheet with four columns labeled "Reportid," "class," "mail," and "CD," containing various data entries including IDs, classifications, mails, and corresponding CD values, with one CD value marked as #N/A. (Captioned by AI)

Sequence18.xaml (15.6 KB)

Regards,

10

You really shouldn’t be trying to do VLOOKUP. What you should be doing is reading both sheets into datatables and then using activities (like Join Data Table, Filter Data Table, etc) to do the data manipulation.

11

Studio x doesn’t have those functionalities

12

@govindreddy_nagireddy

Go to activities → filter → select developer you would see datatable related activities

Also if you want direct on excel then you can use excel formula only and write it in first cell using write cell/formula and then use auto fillrange activity to drag formula till end to know after lookuo which are matched or not

Cheefs

13

When I filter as developer in studio panel it is showing warning symbol saying that it might not work as expected

14

@govindreddy_nagireddy

as of now that is not a problem…that warnign will not stop you from running

Also I have given you another approach as well

cheers

15

Here in write cell it is entering the likewise shown in the image instead of path it is entering the variable

IMG_4937
IMG_49372268×4032 2.29 MB

IMG_4936
IMG_49362268×4032 2.33 MB

16

@govindreddy_nagireddy

Click on Open in expression editor and give your expression in that

Like below

The image shows a workflow step in an automation tool using an Excel file, with highlighted areas indicating errors in the "Write Cell" action where values need to be specified, and an option to open the expression editor. (Captioned by AI)
The image shows a workflow step in an automation tool using an Excel file, with highlighted areas indicating errors in the "Write Cell" action where values need to be specified, and an option to open the expression editor. (Captioned by AI)889×591 59.3 KB

The image displays an Expression Editor window with a formula input for the Write Cell activity, containing a VLOOKUP function that references another Excel file and sheet. (Captioned by AI)
The image displays an Expression Editor window with a formula input for the Write Cell activity, containing a VLOOKUP function that references another Excel file and sheet. (Captioned by AI)817×569 12.7 KB

"=VLOOKUP(A2,[" + OutputFile + "]Sheet1!$A:$C,1,0)"

The image shows an automation workflow where an Excel file named "Excel2.xlsx" is used and a VLOOKUP formula is written to cell D2 on Sheet1 within the same Excel file. (Captioned by AI)
The image shows an automation workflow where an Excel file named "Excel2.xlsx" is used and a VLOOKUP formula is written to cell D2 on Sheet1 within the same Excel file. (Captioned by AI)754×601 20.1 KB

Regards,

17

I did in the same way

IMG_4938
IMG_49382268×4032 2.25 MB

18

@govindreddy_nagireddy

This image depicts a sequence workflow in an automation tool, involving an Excel process scope that assigns a file name, uses the Excel file, writes a VLOOKUP formula to a cell, and performs an auto-fill operation. (Captioned by AI)
This image depicts a sequence workflow in an automation tool, involving an Excel process scope that assigns a file name, uses the Excel file, writes a VLOOKUP formula to a cell, and performs an auto-fill operation. (Captioned by AI)853×1168 49.9 KB

NewBlankTask.zip (268.6 KB)

Regards,

19

What is the datatype you have given for output file is it string or other for me still it’s entering the path in the write cell with double quotes which is given in write cell activity

20

@govindreddy_nagireddy

String

Please follow below steps

Regards,

21

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