VLOOKUP Excel Number as Text Issue

Help StudioX
, , , ,
1

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:

  • =VLOOKUP(G86,[EmployeeInfo.xlsx]Sheet1!$A:$B,2,FALSE)

However, if I force it to CONCATENATE an empty string (“”) to the numeric value being searched, it works like a charm. Here is that formula:

  • =VLOOKUP(G86&“”,[EmployeeInfo.xlsx]Sheet1!$A:$B,2,FALSE)
2
  1. Open your StudioX project and add an “Assign” activity.
  2. In the “To” field, enter the name of the variable you want to store the result in (e.g., “MyResult”).
  3. In the “Value” field, enter the formula that concatenates an empty string to the lookup value, like this:

=VLOOKUP(G86&“”,[EmployeeInfo.xlsx]Sheet1!$A:$B,2,FALSE)

3

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?

4

@Anderson_Jason

I hope you are running this in a loop…

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)"

To escape a inverted comma we need to double it

Cheers

5

Hi @Anderson_Jason ,

If you are using studiox could you try with the below activity in studiox.

We can use these ready made activities in StudioX. Give it a try. thanks

image
image874Ă—475 16.1 KB

Regards,
Kirankumar.

6

Hey Kirankumar, Anil and kanmz,

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.

Jason

1 Like
7

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