Multiple lookup values

Hello guys,

I am having an issue while trying to implement vlookup activity into my robot. I have an excel file with 2 columns: 1st column contains dates and 2nd column contains random text. I need to go through the column 2, which should contain text like “text1” or/and “text2” and the rest can be anything. My goal is to get the value from column 1 which is date. In the case if lookup value is “text1” OR “text2” it is easy to get the correct date as all I need is to select data table, lookup value which is f.e. “text1” and select column 2 in target column and create an output value. Now in my case it is something like this:

column1 column2
02.09.2019 TextTextText
01.07.2019 Text1
01.02.2019 Blablabla
01.01.2019 Text2

As I am interested only in the date which is most recent and stands in front of Text1 or Text2 (doesn’t matter which one), what should my lookup value look like? I would like to use something like (“Text1” or “Text2”) but obviously it is not correct. Any suggestions would be really helpful.

Hi Rutkauskas,

First, welcome to the community!

I would look to implement the vlookup directly in UiPath using the Lookup Data Table action.
The output of this action will give you a row index value that can be used to get the value of the corresponding row from column 1.

Assuming that you only want to return a single value, you will still need to perform multiple lookups, and then compare the output of each lookup. You can do that with a loop.

I would structure your UiPath sequence similar to the following:

Read Range (input is Excel sheet containing data table, output is dtLookup)

Assign LookupArray = {Text1, Text2,…} (however many values you want to lookup)

Assign maxDate = 01.01.1900 (arbitrary initial date)

For each item in LookupArray

LookupDataTable (input is dtLookup, item, and ColumnName; output is rowIndex)
Assign lookupDate = dtLookup.rows(rowIndex)(ColumnName)

If lookupDate > maxDate

Then Assign maxDate = lookupDate

Else end

End loop

There are other approaches you could take, but something like this should accomplish what you need.

Cheers!

1 Like

Hi Martin,

Fisrt of all thank you for your quick response. Your explanation was really good and I get the idea now. In the end, variable maxDate should contain the date I am looking for. However I am a little bit confused with date formats. Let’s say in excel I am expecting to always have date in format DD.MM.YYYY. What variable type should I use for maxDate and lookupDate variables? As a result currently I am getting an error while trying to assign lookupDate to dtLookup.Rows(RowIndex)(ColumnName):

Rutkauskas,

You may need to convert the datatable value to a string and then use the cdate function to convert the string to a date value.

The assign could look something like this:

lookupDate = cdate( dtLookup.rows( rowIndex )( ColumnName ).toString )

1 Like

Martin,

Using cdate function helps. However while testing I noticed that this whole lookup process works only if we definitely know that we will have both “Text1” and “Text2” in Column2. In other words it fails if at least on of them is missing. Sorry for not being precis at the very beginning. What I am trying to do is I want to get a date from column 1 having these scenarios:

Only “Text1” exists -> taking date which is in front of “Text1”
Only “Text2” exists -> taking date which is in front of “Text2”
both “Text1” and “Text2” exist -> taking dates which is in front of “Text1” and “Text2” but finally choosing the one which is the most recent
“Text1” and “Text2” does not exist -> taking random constant date, could be the same 01.01.1900

So I came up with an idea to include additional if activity and check if lookupDate variable is not empty.

I would really appreciate if you could check and give your opinion about it.

Fine
welcome to uipath community
hope this xaml would help you resolve this
Rutk.zip (8.8 KB)

Cheers @Rutkauskas007

1 Like

Rutkauskas,

This approach seems like it will work! Nothing in the code jumps out as obviously wrong to me. Make sure you run it through some testing to validate that all cases are accounted for.

As a side note, ideally it is good to avoid nesting too many If statements inside of one another, as it clutters up the sequence and can be hard to follow from a logical flow perspective. If you want to clean up how this logic is displayed you can place a flow chart inside the loop and use the Flow Decision actions rather than If statements, but it isn’t necessary.

Cheers!

1 Like

Martin,

Thanks again for you help!

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