VLookup Functionalit

Help Studio
, ,
1

Hi All,
I want to use VLookup functionality and for that i have two excel, From excel 1 i have one column called - “Service Account Number” and the same column i have in the second excel and based on that column i require column data of - “Cost Center”, “GL”, “Contact Name” from excel 2, How we can do that in a single go instead of using Lookup data table multiple times ?

Thanks

2

@Shubham_Jain2,

You can join the two data tables (from Excel 1 and Excel 2) based on the “Service Account Number” and retrieve the necessary columns (“Cost Center”, “GL”, “Contact Name”) from Excel 2. Here’s a sample approach using LINQ:

  1. Read both Excel files into DataTables using the Read Range activity.

dtExcel1: DataTable for Excel 1 (with “Service Account Number”)

dtExcel2: DataTable for Excel 2 (with “Service Account Number”, “Cost Center”, “GL”, “Contact Name”)

  1. Use LINQ to join the tables based on the “Service Account Number” and select the required columns from Excel 2.

Here’s the LINQ query in UiPath:

(From row1 In dtExcel1.AsEnumerable() Join row2 In dtExcel2.AsEnumerable() On row1("Service Account Number").ToString() Equals row2("Service Account Number").ToString() Select dtResult.LoadDataRow(New Object() { row1("Service Account Number"), row2("Cost Center"), row2("GL"), row2("Contact Name") }, False)).CopyToDataTable

Steps:

  1. Create an empty DataTable (dtResult) with the desired structure (Service Account Number, Cost Center, GL, Contact Name) using the Build Data Table activity.

  2. Use an Assign activity to run the above LINQ query, which joins both tables and populates dtResult.

  3. Output or write the result (dtResult) to Excel or perform further processing.

This approach allows you to avoid multiple lookups and handle everything in a single step using LINQ.

LLM helped me to write this but it’s validated by me.

3

@Shubham_Jain2

Fallow the steps below. If I helped you, please market it as solved.

To perform VLookup between two worksheets in UiPath without using the Lookup Data Table activity multiple times, you can follow these steps using the data manipulation feature in Excel:

Step 1
Read both worksheets:

Use the Read Range activity to read the data from both worksheets and store it in two DataTable variables, say dtExcel1 (for the first worksheet) and dtExcel2 (for the second worksheet).

Step 2
Perform the Join (table merging):

Use the Join Data Tables activity to join the two DataTables based on the common column “Service Account Number”.

Set dtExcel1 as Input DataTable1.

Set dtExcel2 as Input DataTable2.

Select the Inner Join option to combine the data based on the “Service Account Number” column.

In the Join Column field, select the “Service Account Number” column to match.

Step 3
Filter the required columns:

After performing the join, the result will be a DataTable that contains the data from both sheets. Use the Filter Data Table or Remove Data Column activity to keep only the columns you want (in this case, “Service Account Number” from sheet 1 and “Cost Center”, “GL” and “Contact Name” from sheet 2).

Step 4
Write the result:

Use the Write Range activity to write the result to a new sheet or Excel file, as needed.
This allows you to perform the “VLookup” efficiently, using the Join Data Tables activity instead of multiple individual searches.

4

Hi @ashokkarale ,
Thanks, it’s working.

1 Like
5

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