How can I get StudioX to use the second formula below instead of the standard VLOOKUP formula?
I use several Excel documents in my automation and have extensive code to complete the request I was asked to perform. However, I will limit this question to only one part of the entire automation I have put together. I will also simplify it enough so that it is transferable to my current situation without having to write a novel. I believe the basic problem I have is that in one of the documents, a column is being viewed as text even though the cells contain numbers. The part of my automation that uses this data is a VLOOKUP which uses an employee ID # to return usernames from another document. Unfortunately, it does not appear to be working. As I have played around with the documents I learned I can manually solve the problem within Excel by concatenating an empty string (“”) to the lookup value. This forces Excel to view the cell in the first document as text vs. number, thereby matching the format of the number in the second document. Unfortunately, I don’t know how to convert this formula into something that will work for StudioX. I would greatly appreciate any help you can give!
Excel Doc #1
Column A = Username (No data)
Column B = Employee ID
Excel Doc #2
Column A = Employee ID
Column B = Username
I learned the formatting was off when I tried to manually add a VLOOKUP formula in Excel Doc #1, as it always returns #N/A when I use the typical VLOOKUP formula:
Thanks Kanmz! I should say that this VLOOKUP is nested within an IF statement where on the THEN side it skips any rows where the Username is there in Doc #1, but if it is blank the automation should move it to the ELSE side. I need the automation to do a VLOOKUP on these blank username column cells by the adjacent employee ID column. So, when creating the variable, what would I put in place of G86 in the formula?
If so …G is something that would not change only the row number would change…so create a counter variable with integer datatype and give default values as 2 as the first row in excel contains the header…now inside your loop use assign and increment it for each iteration counter=counter+1
And use "=VLOOKUP(G" +counter.ToString+ "&"""",[EmployeeInfo.xlsx]Sheet1!$A:$B,2,FALSE)"
Thank you for your help with this problem. We were able to get it working based on the information you shared. It is built within a loop, and that loop builds out an output based on a couple variables that are created. I will mark as solved. Again really appreciated the suppoty.