UI Path Excel V-lookUp Issues

Help
1

All,
I am trying to write a V-look up formula in one of the cell, and the formula is referencing to another sheet, but when I am using the “write cell” activity for the cell, it is not referencing to another sheet. By any chance any of you know the reason on how I can fix this issue ?

Regards,
Shay

2

Hi @shay
If you use the “UiPath.Excel.Activities.ReadCellFormula” activity ,the result is not get the correct formula.
image
but if you use the “UiPath.Excel.Activities.ExcelReadCellFormula” activity, you can get the correct result.
image

3

You can read your excel sheet into a datatble and use “lookup Datatable” activity to perform the vlookup directly instead of using the formula

4

@shay,

Try appending the entire path in the VLOOKUp and give it in write cell as

“=VLOOKUP(B2,‘D:/Files Path/[ESC V Proposal.xlsx]Sheet1’!$B$2:$D$197,3,FALSE)”

5

I can not do that as the look-up values has wild cards and I have to use excel functions for it.

6

I tried but that was giving the same issue.

7

Can you post the screenshot of the issue you are getting? @shay

8

This is the formula, I am entering

“=IFERROR(VLOOKUP(F2&”“*”“,'[Reference Sheet.xlsx]Solution Steps '!$B$2:$C$24,2,TRUE),”“Submit a ticket”“)”

But excel is not referencing through the other sheet in UI path, it is directly giving me “Submit a ticket” as an output.

9

=IFERROR(VLOOKUP(F2&“*”,'C:\Users\#Robotized Process Automation - Sources\IDOC Process Cleanup\Reference Sheets[Reference Sheet.xlsx]Solution Steps '!$B$2:$C$24,2,TRUE),“Submit a Helpdesk report for assistance”)

Even tried this but even this didn’t work.

10

Even the manual vlookup with this formula will give the same output I guess…
There is error with this, are you trying to get the value of each row and make a VLookup?

Then there are two ways to do that
Easiest way :

  1. Use F2 there and it will VLookup only for F2 row and then use auto fill range activity. The source range will be F2 and the destination will be the number of values you want to VLookup.

Bit complicated

  1. Declare a variable as count and increase the value everytime inside the loop and provide the range as “F” + count.tostring .

and the entire VLookup formula will be

“=IFERROR(VLOOKUP(F” + count.tostring “,'C:\Users#Robotized Process Automation - Sources\IDOC Process Cleanup\Reference Sheets[Reference Sheet.xlsx]Solution Steps '!$B$2:$C$24,2,TRUE),“Submit a Helpdesk report for assistance”)”

11

The formula works in excel, the * is to selected dynamic values from lookupsheet, I tried inserting the formula as a variable and that worked. Don’t know why it was not working when I was inserting values directly.

Thanks to all for your help,
Shay