How to do Excel VLOOKUPs in UiPath RPA (Full Tutorial)

my name is anna jensen let's learn some uipath today we will talk about vlookup or vertical lookup in uipath it is a function that you know from excel and today we will see how we can do that in uipath we will do examples where we make a vlookup in the same sheet and we will do more advanced examples where we'll look up in a table and with different sheets so go download the course materials there's a link to it in the description below it will take you to this site simply just go to download course files and click the link that will get you this excel sheet and first let's establish what a vlookup is so a vlookup is nothing that we want to search for a value here in this case we got an idea we want to search in an area that is this table here so we want to look for an idea and then we want corresponding values from that row when we find that idea that could be name and quantity we want to return that let me show you how it works in excel and it will be very easy in uipath so up here name we'll do a vlookup so we'll say equal then we'll find the vlookup function here so what value do we want to look up we want to look up this id here that is dynamic so if we change this we want it to work then we will take the table array that is this table we want to look for like this and then we will say we want to look here in the first column that is with these idea and then we want to tell what value we want back that is the second column so we look for an idea and then we get back they set the value of the second column so we'll say 2 like this then we can say false so we get an exact match and we will click enter that's it that's how you do a vlookup in excel we could also say 2 here and then we'll get the apple isn't that smart we'll do the same for quantity so let me copy this function since we are allergic to manual work so let's do it automatically then we'll just need to say we want a quantity that is column number three so we'll change this two to a tree and now it works so now you can do lookups here in your excel sheet so for example if i type in four then we'll have the coconut and the corresponding quantity that was easy that was excel now do it in uipath so let me delete these two formulas like this and do it in uipath so we go to uipath and first we'll need to read the data of this excel sheet here so we'll find an excel application scope like this an excel application scope is just to tell uipath that we want to do excel activities we defined our workbook path up here and then in the do we put in all our activities so let me show you so i have my excel sheet on the desktop yours could be elsewhere but find your excel sheet from the course materials that you have downloaded shift right click copy as path then we go back to uipath we could either paste it in directly up here but i'll recommend you to create a variable for it it does not change anything here but when you create more advanced automation it will be a huge benefit so click the variables manager here then create a variable i will call it str excel path like this the variable type will be string and then in the default value here simply just paste in ctrl v like this so now we have it stored in a variable that means that we can use this variable and it will hold this value so up here in the workbook path we will simply just type in str excel path and you can see the intellisense shows up so we can press tab and it will automatically complete it if your intelligence of in for some kind of reason doesn't show up you can just press ctrl space it will show up here you can either click it or tap so over here we got some settings i'll just untick visible that is i don't want visible calculations in my excel sheet then we'll change nothing here and now we will read our data we will first read our lookup value here and then we will read our table down here so still in the excel application scope and with the excel search on we will take a read cell and drag it in remember if you haven't got excel installed on your computer you'll have to use the workbook activities instead of the excel ones it works the exam way exact same way except that you don't need an excel application scope so we want to read a cell what sheet do we want to look in let's take a look in our book so we see that our sheet is named vlookup same sheets fruit so copy that one and go over here here we will simply just paste the name of the sheet in like this what cell do we want to read well let's see it that will be b3 right so still in quotation marks over here we'll say b three like this then we will store this into a generic value a generic value can hold all variable types so if we have a time double text string or anything it will be stored here so we can go over here and then we'll press ctrl k and i'll just say key v gb sorry id and then click enter when we press ctrl k over here it will automatically create a variable so if we go down to variables we can see that we have created a variable here now i just need to change that type and since we can't see the generic value here we just click browse for types like this then we'll just search generic value that's here and click ok so now we change this to a generic value the variable type let's close down the variables manager again so now we will read what we want to look up we will still need to read the table and for that we will take a read range so just drag this in here we will read the same sheet we could you could argue that we want to use a variable for that too but let's just stick to this now so what range do we want to read let's look at the excel sheet we want to read from b6 to d11 b6 to d11 so what we do here is that we say b6 then colon d11 like this we have headers here in our table and we'll keep this add headers on so we tell uipath this read range activity that we have headers in our data we don't want to make the lookup in there but we still need to tell you about that it's there so we again we will output it this time we will output it to a data table that's because it's a range a data table is simply just rows and columns just like the excel table the only difference is that a data table only exists in the memory of the robot so when we click start we will read the range it will exist here in the workflow when the workflow is done the data table will be gone we do it because we can make some very advanced operations on it so instead of just keeping it as an excel sheet we convert it to a data table we will do that 100 times out of 100 when we make excel automations so get used to it so over here we press ctrl k then i'll say dt let's call it dt fruit like this so now we have read our data then we can do the lookup because we have this id up here that's our lookup and we have the table that we want to look in so in uipath let's go over here to activities and find a lookup data table and drag it in we have something over here in properties that we want to specify and by the way if your properties is not showing you can just find it over here and click it to open it first we need to tell where we want to look that's the input data and we created the dt fruit for that remember that's our fruit table then we want to tell what value do we want to look up here we want to look up the value that we got from this cell over here let me open the properties again so you can see we got the b3 here and we will use that value here in the lookup data table so click the properties if you want it to stick you simply just press this pin so what value do we want to look up that was the tv idea remember we created that variable up here in the read cell gvid that was the generic value like this so where do we want to look up because we can either have a column variable we won't use that we can have an index or column name and since our car our area has headers our data table has headers we will look up in the id column so to do that we will go over here in the column name and we can simply just say in quotation marks we can say id like this then we want to say in which column do we want to look to get the value back let me show you that in the excel sheet again we want a name here so we go back to uipath and it will be the same the target column will be name you'll see that we have column index here and here we could have specified the index as well remember it's zero indexed so id that was the first column so that that would have been zero and this one down here would have been one now we just need to store the name that we get into a variable so again we go up here to output we could either get the row out that will be the row that an integer where the row is but we want the value so pick the cell value up here press ctrl k and now we will just say gv name again we go to variables we want to change it to from a string to a generic value so in the variable type click the drop down and now you can see that we have it here because we just used it so choose a generic value like this so now we did our lookup then we can write the result back to the corresponding cell here that will be c3 so go back here find a right cell still under the excel activities so if we search for excel here find the right cell that is here and drag it in so what shape do we want to write to that would be this one you see it might have been easier if we store this into a variable but let's just keep it as it is you can do it so where do we want to type in the value that we got from the lookup that will be the c3 like this and what do we want to type in well that was the gv name so down here say gvname like this so now we created our workflow we can close down this excel sheet uipath will do it for us if we don't do it so let's run it click the drop down here click run file uipath is running that's it let's go back to our excel sheet and you'll see that we have made the lookup with uipath if we have changed this one to say a tree then it would look up this peer column we also want to find fill in the quantity so let me delete this let me save it and go back to uipath to do that it will be an exact copy of this one up here except that we have read the cell and we have read the range so what we can do here is that we can just take the lookup and the right cell so if we mark these press ctrl and click these two here then press ctrl c and below press ctrl v now it's the same but we will do different lookups and to make it sorted we could have moved the lookup up here so we have to look up here and then we write back to excel the first thing that we want to change is the lookup so we want to look up the idea in the data table dt fruits that's fine we want to look in the id that's also fine but the column we want to look in for our get back value that will be the quantity so instead of name we will say quantity make sure you spell it right then in the output we will create a new variable so delete what's in here press ctrl k then we'll say gb quantity like this and press enter so now we have fixed this lookup and here we will write back we will say gv press ctrl space and the quantity do remember to change it down here in the variables to a generic value it does not matter right now but you'll see that it will fail if we don't do it and here we will write back to what cell we will write back to d3 that is this one let's close down this again and right back to d3 like this so now we can run it and we have made a vlookup in uipath actually it's more advanced because we can just specify the column name and it will look everywhere so let's take a look at our data like this we have this pier and the corresponding quantity that's it and now we will go a little more advanced but before we do that if this video helps you you can really help my reach to get to get me out there with my content by just giving this video a like also make a comment if you feel for it i'll be very happy but the most important is that you see it let's go to the advanced example that is where we want to do a vlookup on a table so we want to do a vlookup for several rows at once and we want to look in different sheets so here we want to look up an employee id that could be one two or three and then get the employee name we want to look at this employee sheet we can see that we have it here do pay attention that here we call it employee id employee name in this sheet it's called amp id amp name that's a challenge that you'll face often as an rpa developer there's no magic to it the solution is easy similarly we will look up the price here we will look up the price for these products and we'll do that on the prices sheet like this it's very easy finally we will make a calculation that's not a vlookup but i'll show you how to do easy calculations in excel so why don't we create the solution we go back to uipath and here we got our nice solution so let's create a new sequence so we go up here press the drop down and click sequence we can say v lookup advanced like this and then click create that will give us an empty sequence that's it so again we will read our data but now let me show you we want to read this entire sheet so let me copy everything here and go back to uipath again we will find an excel application scope so drag it in so the workbook path before we get that from the other workflow let's simply just finish off our read range because we have some copied data so find a read range like this and in the sheet name still in the quotation marks paste in like this that will read the range from the vlookup differentiates sales now we go to our previous sequence or go to our excel sheet because this is the part of our excel sheet from our previous example so copy that if you haven't done the previous example simply just go where you saved the excel sheet press shift right click copy as path like this so we go back to our sequence and here up in workbook path we create a variable so we can actually say control k str excel path that will create a string with nothing in it so go to variables and here we will paste in our value like this so now we read it again we will untick visible we don't need that so we read the range of our main sheet that is the one that we want to fill in that was called vlookup different sheet sales we'll also need to read employee and prices so that's it here we will read to a data table so press ctrl k i'll say dt sales similarly we will have two more read ranges for the other two sheets so copy this one paste it in copy again paste it in but this sheet is named differently so go back to excel so this was our main sheet we want to fill that in with data from the employee and prices so here we will copy this one and go back to uipath so now we will read this sheet that was the employee sheet we will create a new variable up here so delete it the output press ctrl k then we'll say dt employee like this again we will go down here and change this since i know it's called prices there's no need to go over and copy it like this go over here delete it press ctrl k dt prices so now we have created data tables where we read our data to let us show that in variables we can see three data tables that's fine and we have the excel path up here so what we want to do is we do the exact same thing as before but we have several rows to do it so first we'll do a vlookup here with uipath then here here here here here after that we'll do that to the prices and then we'll do a calculation so back to ui path to iterate through each row of our main sheet that's where we want to do the vlookup so we first want to look at this row then this row this row this row this row we will use a for each row so find a for each row like this and drag it in it does what it says it will iterate through a row it will iterate through a data table row and remember we have read these things into data tables that was nothing but rows and columns it looks like the excel sheets is just data tables so we can work with it and that is what we do here so in this for each rule we will say for each row in this data table up here and we call that dt sales so go down here and say dt sales like this now we can do the lookup because first we will do a lookup based on this employee id so we will find a lookup data table again and drag it in like here so now where do we want to look well we want to look in the employee because we want to say in the first row we want to say we want this idea and then we make a lookup in employee so let us go back to uipath so we want to the data table to look up in that will be the dt employee remember we are still iterating through the dt sales that is let me explain it again we go through each of these rows then we just take the employee id go over do the lookup here in this one return the name that we get and print it here so that's the first part of our mission so we do the lookup so what do we want to look up we want to look up the value here in the employee id column let's do that so in the lookup value click the three dots up here then we'll say row that is because we want to look in the queue and row so we are inside this for each row we are telling uipath that we want to look in the queue and row this is by the way visual basic i can recommend to books on it that will be in the description too but here we will look at the queue and row that is row and you'll use this a lot in uipath that's the coding language of uipath so that's row that's secured row then we'll say item that is column then parentheses quotation marks you can see that it will automatically create the parentheses end and the quotation mark so here we want this employee id so we just copy this one here go back to uipath and that will be the column that we want to look in because it's from excel we need to convert it to a string otherwise it will be objects so it's just to have it as a string which we can use up here then you'll click ok like this so now we want to say we actually told uipath that we want to look in the dt employee that was this one so we have the app id and the amp name so this one will be the lookup column and this one will be the target column where we'll get the value so let me just copy one of them and id i think i can remember it then go back to uipath so still in the lookup we want to look up in the amp idea so that one will be here in quotation marks say amp idea and similarly we can go down here say quotation marks amp name like this now we just need to store this value into a generic value because we want to get this value that we find here we want to get that back we do it exactly the same way as before so go up here to sell value press ctrl k gv employee then click enter so now we got our value we did our lookup and we do it for each of these rows isn't that smart now we just need to assign the value that we find here we want to assign that to employee name so what do we do here we'll find an assign it will be in the favorites if it's not just search for it it will be here and put it down here under the lookup data table still in this for each row so now we will assign we will still refer to the current row that we are iterating through so that is row then we'll say item parentheses quotation marks what column do we want to make our value go to that will be the employee so we for each row we look here and we'll print back the employee name that's it so in here in these quotation marks print it in what value do we want to print in we will have the gb employee so that is gb employee that will just take the value that we looked up and printed here again to variables go down here and change this gb employee to engineer genetic value always do that because that can hold all value now it will be a string that will be fine but if we had doubles it wouldn't be fine so now we assigned it but still remember that we only assigned it to the data table and as i told you the data table only exists in the memory so we just need one more thing that is in the end of this workflow we need to write it back to excel so find a right range like here take the one under excel activities and put it in here in the end outside the for each row so where do we want to write to we want to write to this sheet like here that will be still in the quotation marks do this then delete the range that will be where we start we will just start at a1 i always delete this unless i have a reason for it to be there our data table to right that will be the dt sales that was the one that we read up here the one that we did the lookup in and the one where we assigned the value to so that will be the dt sales like this just remember to add the headers to keep our nice headers boom that's it you have made your first lookup and it will it will be an advanced lookup so let me close down this excel sheet and run our awesome automation again remember to subscribe to this channel if you like this lesson i promise you i'll make a lot of them so if we minimize this ui path and go to our excel sheet we will now see that we have did the lookup from our employee so we look up this idea and get the corresponding name that was easy wasn't it it will be the same for the price and let me just delete these so we show that it will work in our solution so now we'll do the exact same thing with the price so go back so still in here we can reuse these two things so let us mark them copy it and paste it so the lookup data table that will not be the dt employee remember that will be the dt prices it was called prices but we named the dt prices up here so we take this one here we will say dt prices we already created that variable so no need to press ctrl k this what do we want to well what value do we want to look up well go to our excel sheet now we want to have the price of this product that's here so that will be the product so click the three dots here in uipath instead of looking up employee idea we will have product click ok and that's because this column is named product that's it then we'll say we want to go to prices we have a we have a product that is product and we have a price so we want to look in pratt and then we the value we want to return back that is in the price column so go here so where do we want to look up that will be the product short product the target column that's the one that we want to look in to get the value back that will be price like this again let me show you just so we should that will be this column then we will store it into a variable so let me delete this press ctrl k and i'll say gv price so we created this variable go to variables and in the gv price change it to a generic value like this so now we did the lookup we saved a little bit of time because we copied it from up here then we just need to assign it to the correct row and we're still in this for each row so we take one row each time so we'll say look in the product then we'll get the price from all these things so here in this assign we want to assign it to the price so let me copy this one here again you can write it it's not that complicated but i just prefer to copy as much as i can so here we'll say row item then price that is because we want to print to the price and we want to print the g the price like this that's it so now you did two lookups that was easy and now we will do the calculation because the total column that would will just be the quantity times price it's not a lookup so you can jump over it but if you don't understand this i'll recommend you to do it what needs to be done here is that we'll take still in the for each row we'll just calculate the total so go back here so another assign dragged in yeah i know so delete this and drag it in the sign so where do we want to write well we want to write a row item the total that is the total column like this what do we want to write in we want to write in what's in this row here in this column multiplied with this cell here in the price column so how do we do that well because we get it from excel we get it in objects but we want to convert it to doubles doubles is just a number with decimals and to do that we could either type it in here or we can click the tree dots up here so we will say convert to double so we will say convert dot to double like this then we will say row item then we'll say parentheses like this that is the first one then we'll say multiplied with then we will say convert to double that will that will convert our excel object whatever it's in the price that will be row item that will be price like this so now we make the calculation and it will go into our total column let's try to run our awesome automation to see that everything works like this we go to our excel sheet and we open it here you can see that we have our employee name our price and our total do you want to learn more uipath i made this uipath beginners tutorial 2021 to the left go look that that will teach you everything if you want to go more advanced i can recommend you the reframework guide to the right thank you for watching have a good day bye

Channel: Anders Jensen

Views: 5,454

Rating: 4.9714284 out of 5

Keywords: vlookup uipath, uipath vlookup, vlookup using uipath, uipath vlookup excel, uipath vlookup datatable, uipath, rpa, uipath tutorial, uipath lookup data table, how to do vlookup in uipath, how to do vlookup with uipath, anders jensen, uipath tutorials, lookup excel in uipath, lookup excel with uipath, uipath guide, uipath demo, uipath use case, vlookup in uipath, vlookup with uipath, lookup datatable in uipath, lookup datatable with uipath, anders jensen uipath

Id: vKYkqkEBRE4

Length: 30min 59sec (1859 seconds)

Published: Thu Mar 11 2021