How to simulate Excel VLookup in a Datatable.

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

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:

lookup_value-1.pngStep 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

1 Like