#### Transcripts

1. Basicvlookupintro: Are you familiar with Basic Excel functions? I'm ready for something more. What about Look up? Reference functions? Look up. Reference functions are a powerful way off searching into your data and extracting related information based on criteria that you have, said Andi. If you learn how to use these functions, I can promise you that you will become more efficient in your daily Excel and your daily spreadsheet tasks. The local stands for vertical look up on hates look up stands for horizontal. Look up both of the loco on the page. Local functions are used to find specific items in a table off data. So let's say that you have a table of data. What have you look up to will do is allow you to go down to the list of data in the table and then return data from the right off this column. Looking at this table, we could go down through the stock holds on, find ABC 135 and then say return the quantity and order, which in this case happens to be 12
2. Vlookup: the look of function contains a number off. Arguments on the formula is written. I pretty much like this. We look up and then it's the value that is being looked for after this, the look up table name or the range. And after this you need the number off the column in the table containing the relevant data or the data that you want to return on. Then you want to state whether you want an exact or whether you want an approximate matching. I'll talk about this now in a moment, the criteria that you are looking most being. The farmers left column off the table on the V. Look up and I mentioned this earlier will only look up to the right. The exact match will only return a value where there is actually an exact match and when you're using the approximate match, items in the table must be sorted from lowest two highest. Now the hate look up formula is extremely similar to the V local formula, and we will look at this in more detail when we move into Exelon. As soon as you understand the V. Look up, you will also understand the hate. Look up the view. Look up on the page. Look up! Functions are extremely useful and they could be used in conjunction with other formulas such as the If statement on text functions on this makes them even more powerful. So what we're going to do now is hop right into excel and have a look at some basic fee local and hates Look up example. So here we are now in excel in What we're going to look at first is a basic feel look up example, using an exact much and what I mean by an exact match. Well, the value that you're going to look up your criteria has to match something exactly in the list where you're looking at a for you to be able to return a value. I'm going to show you this now by example. So what we have is we have this table off data here, and we have stuck codes we have in stock. We have quantity on order, coast pair unit, and we also then have the stock value on what we want to be able to do down here is select a stuck cold on when we select the stock hold. We want to be able to return quantity and stuck the quantity on order on and the stock value. Now, what I've done here is I've added some data validation, so you can only select from the stuff codes that are actually here. And how did I do that? Well, I'll show you how right on that first. So the first thing you need to do is go to data and then you need to go to data validation or under your data, tools on in data validation. Start a validation on under settings. In this circumstance, I've selected list on my source has been the stuff codes from the list within my table. And that's how I generated this drop down for you to actually select. You're stuccoed. So now we want to calculate the value in stock on Dwyer. Looking after the stock hold, ABC one tree one. We want to see the quantity that actually in stock. So if we could appear, Here's the stock code. Here on we have 39 stuff we 15 on order four pair unit on the stock value is 156. So let's start RV look up formula. So as always, we start within equals and then it be, Look up. Once you find the formula on, it highlights and blue like that you compress tab to accept you, then greeted with your screen tips on the first green tip is the local value. So this is the value of the criteria that you actually want to look up, and we have this here in South B 20 so we'll select Cell B 20. If you were manually typing in a hard coating in the value cause it's text you need to put it into inverted commas. After this, we need a comma on it's looking for are table array. So this is the table in which we're going to carry out our look up. See, you need to select the whole table off data repress comma on our screen. Tip jumps. Two column index number. Now watch this column index number me. Well, that means what's the number off the column that you wish to return the value from So are stuck code is the first column in our table of data that we selected. The quantity in Stuck is our second column so we want to. Polian. Our second column on DWI will press comment. Now it's looking to find out if we want an approximate match or an exact much, and we want to select exact match because we're looking for this exact dark cold. We can close the bracket and hit. Enter on what you can see. This 39 much is this. 39 appear and it's pulled the value win. Correct. Now, if we change our stuccoed and let's just pick the very 1st 1 for simplicity, we can see our instruct value automatically updates. And this Cumbie very, very dynamic, useful tool tohave. Let's put in a V look up to pull in our quantity on order. So equals be local hand are local value, which is are stuck hold our table array again is the same table. Now the table The column in which you wish to find you're stuck cold in which you wish to find your criteria should be the far most left. Cullman. Don't forget, you can old. We look to the right using V look up all its own. The column index number is next on in this case, the quantity on order is the third stuck code in Stuck on order that hurts to repress tree . We're going to take an exact match on hit. Enter on. We can see we have a tree on order. Now let's pull in our stock value and I look up value again. If they're stuck, hold our table array. Our column index number this time is stockholders. One in stuck is to on orders tree cost per unit. It's four. Stock value is five onda. We want an exact Martin it enter and we could see our value of 348 has pulled in. That's elect another stuccoed. We go right down here to the bottom. We've selected this one here when we can see our in stock quantity of 36 matches. 36 the 11 matches the 11 and 144 matches the 144. So you can very quickly return values that you need by using the bee local with the exact much Okay, so the approximate match. Let's have a look at this table to explain the best. We have quantity ordered and we have discount allowed and we get a discount break dependent on size off the order that we may. So if we order between 500 1000 units, we get 2%. If we order between 1000 in 2000 we get 4%. Between 2000 to treat hasn't 6% on 3000 to £4 will be 8% of 4000. Hope will be 10. Now, the thing with doing an approximate you will need tohave he sorted in order. So if you're using number is it will have to be a numerical order on. If you're using ladders, you'll have to sort them alphabetically. So what's gonna happen is we want to find her discount allowed based on an order quantity of 1750 we want to be able to pull in the discount allowed using Villa Former if we use the normal be look up with the exact match. So we look up the local value Is the ward the quantity ordered on? We want our table array. We want a column index number, which is two and we want we're going to say an exact match. Close the bracket on his injured and we get in a because there is no exact match. Now I'm gonna press F two there to bring me back into the former. I'm gonna change the end of this here. I'm gonna delete that whole. This time I'm gonna pick true, which is approximate match. I'm gonna close the bracket on Hit. Enter now it's pulled in 40%. So what's gonna happen is it's going to go down here and it's going to say, Is it greater than 500? And the answer is yes. So it moved is a greater than 1000 on The answer is yes. So we're going to move to the next is a greater in 2000. The answer is no. So it'll go back to the one tales and and I could pull in the percentage from here on that basically, how the approximate mass is working. So we changed this value treat has 500 will see that we get an 8% discount if we put in thought. If we put in 7000 we'll see we get a 10% discount on if we only put in 100 would see that we don't get any discount at all so that's the approximate match. So remember, when you're using the approximate match, you'll have to have the sort of from the lowest to the highest number, and Excel will go down true and see if it fits that criteria move onto the next one, move onto the next one and then they could, because it didn't pass the criteria for this. And that's the basic feel cope for an approximate.