Vlookup in UiPath

Very excellent explanation. Could you please tell me how we can place the “Not Found” word in the unmatched vlookup values in UiPath. Like in excel, when lookup value not found in lookup column so it places #N/A in the cell.
Thanks,

How should I public please??

Hi

Welcome to uipath forum

Are u using LOOKUP DATATABLE activity for Vlookup

If so we can get the output as Rowindex whose value will be -1 if the lookup value can’t be found

So use that output and with a IF condition like if the rowindex is -1 then write as “Not Found” in that row cell

Cheers @Dr_Raza_Abidi_PhD

Thank you very much @Palaniyappan . I am using for each row loop and then lookup table then write cell. So as per your comment, I inserted if condition after the lookup table step. Below is the condition I have placed:

If DT1.Rows.IndexOf(row) = -1 then write cell …

Compiled successfully but no result.

Many thanks,

1 Like

This is rowindex of current row under iteration in for each row loop which will not be obviously -1

Inside the loop you would have used LOOKUP DATATABLE activity
In that activity we have a property called rowindex
Get that output as a variable and then use that in IF condition

Cheers @Dr_Raza_Abidi_PhD

Great, Indeed, I have used the lookup activity inside the loop and I have found the RowIndex property and what should I put in that property. It is asking about the row index of the found cell?

Fine

As it is a output proeprty create a variable there in that property by clicking ctrl+k and name it as int_rowindex

Then inside the for each row loop use a If condition like this
int_rowindex.Equals(-1)

If this is true it will go to THEN block where use a assign activity like this

dt.Rows(dt.Rows.IndexOf(row))(“your columnname”) = “Not found”

Cheers @Dr_Raza_Abidi_PhD

Thanks, I am near to success. Pl explain with assign syntax pl. Because assign requires variable.

Here in the left side

dt.Rows - will give list of all data rows which is each row in that DATATABLE

dt.Rows(rowindex)(column index) - this will give the value of particular row and column, cell position
Row index will start from 0 for first row and column index will start from 0 for first column

To get the row index inside for each row loop
We use this
dt.Rows.IndexOf(row)

And column index can be mentioned with columnname

That’s it

@Dr_Raza_Abidi_PhD

Thanks, you told me that put the above code in Assign activity. I had done, Pl. find below the complete code:

IF int_rowindex.Equals(-1) Then
Assign Activity
DT1.Rows(DT1.Rows.IndexOf(row))(“My_Column_Name”) = “Not Found”
Write Cell
“File Path …”
“Sheet1” My_destination_range
Output // This is the variable position so here, I will place the Assign activity variable I think? But how the assign variable will create. If I create so how could I place the above DT.Rows code in it??

This will actually write the value to DATATABLE as not found for those the values are not found
And after that we don’t need write cell activity

Once after the for each loop we can directly use write range activity where pass datatable as input

@Dr_Raza_Abidi_PhD

Thanks a lot @Palaniyappan

1 Like

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