How to simulate Excel VLookup in a Datatable. Filter Datatable---UiPath(RPA)

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.

This is how it's done with UiPath:

Step 1: Read The Excel File

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.

Step 2: Set the lookup value

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

Step 3: Lookup Using For Each

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

Step 4. Lookup using Datatable Select method

Key points:

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

 

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