How to use Lookup DataTable in UiPath

Help Activities
1

In this tutorial we will learn about the vlookup function & its implementation in UiPath.

*Let’s refer the below excel data to lookup the Item & Price value.

image
image1100×634 84.6 KB

We all aware about how to use the Vloookup function in Excel.

= vlookup (lookup value, table range, column index)

Now, we will use the same formula in UiPath to lookup the values.

Implementation in UiPath**

  1. Open the Excel file in UiPath using “Excel Application Scope Activity”, store the excel location in the variable . Lets say strExcelLocation.
  2. Read the range from the excel file using Read Range Activity from B6 to D10 & store it in a variable DTRange.

image
image707×435 9.87 KB

image

3. Read the cell B2 from the file i.e lookup value & store it in a variable strLookupData.

image
image655×516 11.1 KB

4. Drag & drop Lookup Data Table activity & pass the values.

This lookup data table search the value of price in the table.

image

image
image1100×613 126 KB

Now, we will use another lookup data table to get the value for Item. The Only change would be in the Target Column.

image
image547×504 10.8 KB

image
image984×616 23.4 KB

5. Finally we will use write cell activity to write the data back to excel file at their specific location i.e C2 & D2
image

image
image994×261 15.3 KB

Above are the variables & their types used in the process.

Let’s run the process now & check the result.

image

I hope you enjoyed the article!
Happy Automation!!

6 Likes
3

Hello,

FYI - UiPath actually has a VLookup activity https://docs.uipath.com/activities/docs/v-lookup-x

Best regards,
Marius

1 Like
5

When you have thousands of rows in an excel, how would you do it without using for…each…row?

6
7

Sourav,

I’m afraid I don’t see a For Each Row in the original post. A Lookup Datatable activity is often used with some sort of Write Range, in order to update existing records. I’m afraid your question needs clarification and probably is better suited in a new thread of its own. Would you be against creating a new forum post of your own, where you can add the details of what you’re trying to do?

8

Thank you for your explanation, it was very clear, I will try it in my automation and then I will tell you about my experience.