Resources

Knowledge Base

How to simulate Excel VLookup in a Datatable. Filter Datatable.

Note: This Workflow was created with the 8.0 version.

Given this Excel file, we want to retrieve the value corresponding to a given index.

Excel.pngThis is how it's done with UiPath:

Step 1: Read The Excel File

readrange.jpg

I used the Read Range Activity. Be sure to check the "Add Headers" property of the Read Range activity in order to be able to use column names later in your automation. Also, I used the workbook retrieved by the Open Workbook activity; the data is stored in a datatable.

readrangeoptions.jpg

Step 2: Set the lookup value

This is a simple assignment operation where we can change our lookup index:

lookup_value-1.pngStep 3: Lookup Using For Each

for_Each.pngThis is quite self explanatory. The key here is to set the correct condition: row("Index").ToString = indexToLookup 

Step 4. Lookup using Datatable Select method

Datatable_Select.pngKey points:

1. The select string is initalized with "Index = '{0}'":

selString.png 

2. The string.Format that will return the correct select: "Index = '2'":

3. The datatable.Select activity that returns an array of rows:  dtInput.Select(selectString)

Workflow attached:

VLookup.xaml

Input.xlsx