Download Your Free Invoice Template for Google Sheets Tailored for Product Management

Invoice template google sheets free for Product Management

hey guys welcome back to another episode of spreadsheet Basics and in this lesson today we're going to talk all about V lookup you'll also be able to download a free quote maker from me the links are in the description below and these are fully customizable and you can fully use it once you've actually copied it to your drive so without further Ado let's get started shall we so before we jump into our spreadsheet or Google sheet I'm just going to briefly explain to you what a v lookup is so this is one of the formula or a function that exists in a spreadsheet it allows you to basically search data in a database or a name range or a range you will be looking data vertically and vlookup has been one of those function that has helped me do my database from let's say in terms of like the quote maker that I did just then for you and when it comes to database this was something that was really powerful for me so let's just call that we have a data set or a database right and the database can be with a title and also a data set which is belonging either with the row right so it can go down or it can actually go horizontally so you actually have two ways going horizontal and also vertically now we are going to search our data vertically that's why it's called the vlookup vvo vertical right and we're going to want to do that specifically towards our data now once we actually have the data usually we have something like this so let's just say there's three students called Edwin edua and Edam and they're in three different classes and they all achieve three different scores now we can actually look up different datas in here by using a lookup but vlookup is very important because when you are using a lookup it's generally better if the data has been sorted now a vlookup can be used instead if your data is not sorted properly let's just say that we want to actually look the score of Edwin right so we can actually use a v lookup and say hey V lookup can you actually put the cor data of the the person named Edwin in inside this database right or edua or Edna and you can keep changing the search key and you can just keep going and on and on and on and you can also look for a different role let's just say there's a question is which class does Edwin belong to and Edna or edua and in this case Edna is in class number three now we want to return those values so we can actually use them as calculation or some kind of data in in the future the thing with database is that it's beautiful it's not hot written it can be also extracted from a different source and your data can be very flexible and when it comes to extracting it you can also change the data and it will reflect itself within those queries as well and it's just wonderful for me when it comes to let's just say inventory or let's just say when when we do a quote and then there's a price change and of course we can actually input those and the change will be reflected straight away in the quote so you don't have to worry about the price change if you just change the database and then the quote will update itself right now things that you can do with this is you can make invoices you can make inventory and you can do a lot of things in the financial realm of course and so with that further Ado let's just go into vlookup so vlookup just like any functions has parameters inside them that you actually have to input so vlookup has four parameters that you can input the first one being the search key the second one being the data range the third one being the index and the fourth one being an option whether you want to actually find the sech key to be exactly match or the closest match now you can input those in an order of a b c and d and you can actually leave out this last one if you don't want to fill it in or if you if you just want to make sure that it's false you can actually just input false as well so normally you can have a v lookup that has three parameters inside and it will look like this hey guys I've hope you've liked the lesson so far and if you do please don't forget to hit that subscribe button so you don't get to miss out on any of the future videos that I do make all right let's get back to the lesson shall we so so we actually have a very simple range or a simple database here that I can just highlight we actually have a vlookup function that we're going to input in cell B4 and as we have previously mentioned that there is a s key a range that must be applied and also an index and whether or not we want it to be sorted or not so let's just say that I want to return the names out of these database right so first uh I'm going to search for who's the name of the person number three and I'm also going to be saying hey the range is going to be from C8 into D1 and then the index is going to be second because being the name as the second column of the database so the range has two columns from C to d right so we're gonna say hey please return the column index of two right and of course we're going to use the sorted function which is false so we're going to go like this we're going to go equal V look up right we're going to press Tab and then we're going to say the search key this time we're going to sell reference the ones that are up there so we can actually change the sech key later on the Range we're just going to go drag from number one to Ed now win and it will apply C8 to d11 so that's the database and please do not include the title of the database right so the number and the name should not be included in the range and you can say comma again and then we're going to say the index is two all right now it's going to return add now because the our search key is three it has found number three and then it will return Edna as the index number two now if we actually change this index back to one right it's going to return three because it's going to look for the First Column which is the number column here so we we don't want that we want it to be the name so we can also change our search key into one and will return Edwin two it will return adua and then four it will return and no win but if we actually input the wrong one or a search ke that does not belong in there it will return well probably close to the sorted one now this is where right the option function is truly best now we want this to be false right now as you can see it says error it did not find Value five in relookup evaluation now why is this important because sometimes in our database we want to be exact in our search we don't want it to be wandering around everywhere and we want for it to return an exact match of the value we don't want anything else other than the exact value and if we actually return something else that is not within the value sometimes we get the wrong data so what's the problem with having true so let's just say that we have true here right it will actually return one of the closest match possible 3.5 it would it will return Edna right 3.6 s it still returning Edna although it is closer to Edna win not Edna right cuz you only have3 towards Ed wi it still is not returning that value right even 3.9 it still is returning Edna here so another example here would be something that I use the sorted as true and we have more numbers so we're actually working up to a th000 and the database goes from 100s and and so on right so if we actually change our s k right from one it will return nothing if we actually have 100 it's still returns nothing until we get 112 which is Edwin right so we go 110 again it's still is not available so it actually looks for the exact match with the value that is below that and we can also say that 224 it will return adua right right here now there's Edna and Edna win now Edna and Edna win is is only two numbers apart so they are 3232 and 334 right so if we actually put 333 it will still return Edna being the closest and also round it down now Edna win is also the same right so if we actually have 334 of course it's going to return the exact value which is Ed now win but when we have something like 350 it will still return at no win so uh sometimes it can be a little bit bit of a mismatch let's just say we want to know who's got a th000 right it will still return edno even though he got 334 so this is basically like whoever's got the highest number below this number will be return as a result so this is where I don't really like vup using a true is sorted function just bear that in mind when you're doing database okay this is my free quote maker that I've shared with you in the description below and basically you can input anything you can input logo here and you can change the title here and you can actually change this into a PDF later on if you want to give this out for your sales have a go at it and I I personally like this because although that exists an X lookup basically this is a double v lookup and I know a lot of people going to complain to me hey you know why should you be using a double v lookup so as you can see up here there is a v lookup inside a v lookup right and you don't have to understand what it is yet as of now basically what I'm trying to do is to actually be able to look up the service name instead of using the stock keeping unit because sometimes you know when you when you're doing a quote you don't want to remember whether or not hey you know what sample photography is on SKU number one or you know I I I've been through that where people are actually looking for stuff using an SKU I mean it does pinpoint you into that certain SKU or barcode itself but it's just nasty when it comes to searching so I've actually developed this double v lookup uh back when I was developing a database for someone right and basically you can actually input your item number without scanning a database a barcode or anything as long as you remember some form of the name so in this case right let's just say a master SKU right here I'm going to input a um let's just say Excel lence right Excel lessons is going to cost you 3 million rupia all right and I can go back to my Cod maker and I can actually input my Excel I can type Excel and it has a datab validation below so I can just press down right and I say tap and it will say Excel lessons right so and then I can input the quantity let's just say there's two people applying for the Excel lesson and it will automatically gets applied into the price which is 2 * 3 and it becomes comes six and this is wonderful for me because back then I used to have to search for the search key not just by using a name or somewhat close to the name and you can directly apply this towards your invoice now this itself is I I I reckon it just saves you a lot of time and of course you can change the color by adjusting the custom color here let's just say when you go to alternating colors you can adjust it to green and of course you can change the borders Colors Let's just say no not red we're going to go with green okay and then we're going to apply this right and we're going to apply the top and bottom and as you can see I've already changed the color of the borders as well just play around with it and of course you can always customize it later on now to download this you can just view it straight away from the link and you can go to file and say make a copy and you can make a copy of it into your own drive and you can start customizing it and of course use it straight away as you go of course sometimes you want to change the price into dollars as well or any other form of currencies so yeah just play around with it and hopefully you get what what I mean by be lookup so hopefully that has been very helpful for you in your spreadsh journey and I hope to see you again in my next lesson all right ciao