Excel VLOOKUP Function - All You Need To Know

welcome to vlookup week video number one and this video here this is going to be an epic video 25 tips from absolute basic beginner vlookup all the way to advanced so this is like one stop shopping for how to use the lookup now there's going to be very long video so if you click on the link below the show more button below this video there'll be a table of contents with 25 topics and minute mark so you can hyperlink click on the minute marketing at YouTube and it jumps to that section of the video alright so we're going to start right off the bat vlookup what does it do well the V means vertical if you used H lookup the H means horizontal but wait a second what does vlookup and H lookup mean anyway and why are they so common well the bottom line is everybody needs to look stuff up so for example invoices sometimes you need to look up a product you're selling and find a part number the flight range these are boomerangs here flight range out or price this is vertical because the item we're looking up we need to tell the vlookup function hey we have the belen boomerang and we need our vlookup to tell us how what the price is this is orientated vertical so you use vlookup this one is orientated horizontally now right here this would be the lookup or the part number here but because we're looking it up in a horizontal and then returning something from a row that would be H lookup another example of commissions right you may have various commissions you either want to show a category like how what type of sales you had above par very good excellent or you might get paid a certain commission taxes you might have to look up an income amount and return a tax rate or taxes from previous brackets human resources you might have an ID and then you need to return last first email and phone so lots of examples all vlookup and H lookup do and other functions also this video is just about vlookup and one example of H lookup throughout the rest of the week we'll look at some of the other lookup functions but all of the lookup functions what do they do they look stuff up now let's start right at the beginning vlookup what is it do it delivers a value to a cell now we're going to look at exact match lookup first and then later we'll look at approximate match now what is exact match mean well if I type quad here I want the price just to pop up so I'm going to put a formula in this cell it's a vlookup and it will look here and it will get the correct price and deliver it to the cell so vlookup delivers something to a cell now the way vlookup works is exactly the way we humans do it now let's think about how we humans do it we look at this word quad we put it in our brain we remember we go up to the we know where the table is right usually it's printed out next to us or something like that you go through the first column when you find the thing that's in your brain here that tells you what row in the table has the potential data now once we know the row we also need to know the column our price is in the 1 2 3 4 column then we take that number in our brain we come over here and we type 35.9 5 now I want the formula to do it automatically so we're going to use vlookup V for vertical lookup because we're looking stuff up now the nice thing about the screen tip it's just like we humans do it when we do it by hand you first need to tell the vlookup what are you looking up called the lookup value you have to tell it hey please put quad in your brain vlookup spring comma now the next thing you got to tell vlookup where the table is now I'm going to highlight this entire table first column always has the lookup value and then subsequent columns our data to retrieve so there's the table array comma column index well just like we would know one two three four the fourth column has our price we're going to type a four here comma and then the lookup range either true approximate match we'll look at that later that's the default you never have to type that and you just leave that out it's when you want exact match which we do here you put a zero you can certainly double click there and it puts false but zero will always work alright so there you go one 39.95 now the cool thing is if I change this to Carlota boom it changed now we may have an invoice set up we have price times units so I say equals price times unit that's our first example of an exact match now watch this so I'm a really bad typer and then once in a while I type the wrong name here so there's a couple things we're going to do to fix that notice what does vlookup do if it can't find the exact match it's very polite it says not available all right all right so let's type this down here we're going to see what how we can amend our formula here to show a message that says you have the wrong lookup value so down here we're going to do our same vlookup Remer now in 2007 or later if you type your function name after a while you get the hang you know that VL will immediately show us in blue this drop down that's the vlookup then you can just hit tab there's our lookup value comma to get to the next argument there's our table comma to get to the next argument it's a 4 comma and 0 all right so again we want when we miss type it to show something different than na no problem in 2007 and 10 you can use this great function called if error if error it simply allows you to put your function or formula inside of the if error you come to the end comma to get to the next argument and now you tell it what you want to display and since we're putting text into a formul you have to put it in double quotes now one thing about texts and formulas is that if you enter this formula without spell checking it like right now it says incorrect boomerang nem if you did run spell check it will not catch it because it's inside a formula so you actually have to run spell check while you're in edit mode this keyboard shortcut for spell check is f7 okay so I guess NAMM is a words and how you got let's try this f7 so there are found incorrect that's incorrect all right so now it says incorrect boomerang name and you could put whatever message you want there and then you type it and boom now another even another way to deal with this problem is to use something called data validation list now notice the only things we're ever going to want to put in that cell down there are from this column of the first column our lookup table right all these words here so I'm going to come down here and do data validation list which means there will be a drop-down and we can select and it will only pick from here so down a validation is on the data ribbon data validation and then that right there the keyboard shortcut is alt DL all DL allow allow what not any value how about a list or a source and then we simply highlight this up here and then click ok so now we have a drop down and so we're not going to make a spelling error and then we could go ahead and do our formula equals vlookup we got our lookup value comma our table array comma 4 comma 0 all right now notice we keep having to highlight this range over here one way you can get around that is to use named ranges and so we're going to do two different ranges we're going to create a named range for our data validation and for our vlookup table now this is the name box the name box is to the left on the formula bar you could simply highlight and come up here and type whatever name you want like DL boomerangs or something like that and then hit enter right so now you can test it by clicking somewhere else and then now the name box has a drop-down and you can select and it actually jumps to that area alright so now we can highlight this whole table and then up in the name box we can type I'm going to type V table enter now we have two ranges up here and sometimes it's handy to know how to edit this the keyboard shortcut and I do have some notes over to the side here so we just we're down here and we want to create a data validation and view lookup using named ranges now before we do that let's look at how to edit them the key let's see so it's under formulas name manager the keyboard shortcut is ctrl f3 and so then you can select whichever one you want edit and we don't need to add them both of our names are correct now let's do data validation alt DL tab I'm going to type the L key to get to list tab and I'm going to use the f3 key f3 notice it was ctrl F 3 to get to the name manager f3 gives me the option for pastes names so I want D album ranks there it is and then I click OK so I can select that now let's do our vlookup equals VL we have I hit the left arrow to insert that cell into the formula comma and now I need my table name so I'm going to hit f3 and I'm going to say V table comma 4 comma 0 now there's another great advantage to using define names let's scroll over here and I have a little invoice template here now let's go ahead and do our V lookup so if you look up we are lookup value is going to be our product name comma our table I'm going to hit f3 double click comma actually let me show you a different way if you know how it's spelled you type V T and notice the icon is function for functions and a little dog tag for names so V T and then you can hit tab comma index the column index is going to be 4 comma 0 now that cell reference J 25 is a relative cell reference the formula is right here but this formula because it's a relative cell reference will always look on to cells to my left but this when you give it a defined name it's locked this will move relatively this will be absolute so when I control enter and drag it down notice when I click f2 to put it in edit mode that cell reference is moving as I copy but that's not that's locked if you come up here you can actually even see a little preview of the green there I mean and it's still highlighted right so one advantage for defined names is that it's absolute when you copy the formula it's locked in comparison to a relative cell reference which moves now in our invoice this is a perfect situation we we don't want that na now a couple minutes ago we saw how to use the if error but let's see how to use the if function because in this situation we anytime the cell one two to our left is equal to blank then we want to show a blank so we could do this use the if function and the logical test would be anytime that cell right there and we use the comparative operator equal and blank is denoted or the syntax in excel is double quote double quote so that's the logical test inside the if that comes out true or false comma the value is true that means what do we want to put in the cell if it is blank I'm going to put double quote double quote comma otherwise if that's false which means something there then please run the lookup and now we can drag this down now that's important because in some cases if you're working up here we saw the if error right so if error only exists in seven 2007 and 2010 so in some cases where you don't have something to trigger it like that blank over there you actually might have to use the if function so here I'm going to say equals if and the logical test here I need to say is the vlookup and error so I'm going to say is n a is an a is a true/false function it delivers either a true yes it isn't in it is an n/a error or it's not false so I'm going to put that vlookup there right again this is what you had to do in earlier versions that's the logical test this only comes out true or false well when it is an n/a what do we want , the value of true is I'm going to put double quote here you could put any message you want inside there otherwise the value of false I'm going to paste that vlookup and so that's how you had to do you had to do something similar to this in 2003 or earlier versions right so if I put something like that in correct name maybe I want to put that same little I'm copying it in edit mode and then down here I'll highlight that in control V kind of nice to have that data validation alright so back over here that was a nice solution there and then of course you could run your total column there for your invoice all right one other situation for vlookup I'm going to do alt DL tab l tab f3 is sometimes we want to select a particular product but we don't want to have we don't actually want to show the price so here in these formulas we're just using vlookup to deliver a value to the cell but now we want to combine so I want to have that number show up in a formula so over here if we want a total we'd have to say that cell times this cell so we get 95 85 but what if you don't want this cell for whatever reason you just want the total the units and the total so I select majestic Butte no problem we can literally use this inside of a formula so here whereas here it's delivering a value to the cell here in this formula we're going to have vlookup deliver a value to the formula so I'm going to say vlookup this comma V T comma 4 comma 0 I'll enter this and we can see it gives us 30 195 now it's delivering something to the cell now I'm simply going to take the result of vlookup multiply it times that and there we get in a single cell or answer now there's a great feature under formulas evaluate formula right here the keyboard circuit that I memorized is tough tu F alt tu f o tu F that's really tough whoops o tu F the great thing about this evaluation is you can click on this or hit enter and watch the formula value now watch vlookup if you look up is simply delivering a number to a formula so let me get our result right now H lookup this is a rare situation but occasionally you'll have a table orientated horizontally now I didn't mean to have this table set up this way so I'm going to do a little trick here I'm going to highlight the whole row point with my cursor like that holds shift and drag up when you download this it'll be like this correctly all right so let's put our Belen I'm going to do horizontal so H L exactly the same except for it's going to be looking something up in the first row and then returning a certain row number so the look of value this comma the table array with H and vlookup you never highlight the field names there comma it's still for one two three four but now it's row comma zero right so if I change this to Carlota it changes alright so that is a bunch of things about vlookup an exact match now let's go look at approximate match approximate match is great again we're going to do the same thing vlookup is going to deliver value asset to a cell but we'll be doing approximate match now here's a situation you have some sales and you had 7500 let me use control shift for to format that so you're going to look down here you're going to realize 7000 I'm above thousand but lows above 7000 but less than 10,000 so my commission is going to be 250 bucks so I need to use this table to deliver that 250 here now look at this this is different because there's some gaps here's our first column and there's gaps so it's a number value or looking up right but there's some gaps in between here now literally here is a way here's a way to think about how the vlookup function is going to work in this situation you're going to take this in your brain you're going to race down the first column and you the first bigger number that you bump into so I'm going to bump into 7000 that's the first number bigger than 7500 then it jumps back to the next row then it knows which row and you can either deliver a category or commission paid or whichever column you want now technically that's not the way the vlookup and other lookup functions do this approximate match but it's a great way to think about it especially when you're learning now here's how vlookup sees it for this category right here notice it's going to be the sales are going to be greater than or equal to the zero less than a thousand so if it's exactly zero it's going to be included here everything up to but not including a thousand so right here if you have exactly two thousand five hundred your commission is one hundred dollars all the way up to but not including the seven thousand the last category I had a formula here the lower the actual amount for any category is always included so the zeros here that one's a thousand here two thousand five hundred or ten thousand here alright so but this last cordis category is slightly different it's greater than or equal to ten thousand so another important thing about this is this first column must be sorted from smallest to biggest so here's a little note right here not only that but this is a if we give it a lookup value here meaning your cells sales less than that first number in the first column here it'll give you an n/a now in our case we're not going to have any negative numbers but that's what it would do and we'll test it and see all right so let's go ahead and try this equals vlookup the lookup value is going to be this comma the table array comma the column index I want one two three and since true or one is the default if that's what you're doing just leave it out notice this square bracket here anytime you see a square bracket in a argument screen tip here it means you can leave it out and it will assume the default so we're going to assume the default so that makes approximate match a little bit shorter formula here now let's test it how about zero how about 2500 all right how about six thousand nine hundred ninety nine and ninety nine pennies ah one penny short of the big jump from one hundred to two fifty seven thousand let's try minus five and a again it's going to give an n/a if it finds something smaller than that and then let's try something really big right all right so it's going to anything equal to that or greater for the last category we'll pick that up so that is approximate match now let's go over to this next sheet the next topic we want to talk about is how to do vlookup when the table is on a different sheet notice we have a different sheet right here and when the table is in a different workbook now when you download this workbook you can download vlookup Shark Week this one but you can also download the sea table all right no problem you can do it a couple different ways you can use sheet references or workbook reference or you could even use a name for for example you can name this right here but we're going to use sheet references alright so vlookup with a table on a different sheet equals the L tab your sales oops let's type in some thousand five hundred control-shift for for currency copy control C control V for paste alright so equals vlookup I want to look up this value comma the table array now the table is not on this sheet so all we have to do is click on the next sheet and then highlight I now noticed up here it's this table exclamation point it's being polite inside the formula it's putting the sheet name here so you know where in fact this table is from and then I'm going to highlight that table a six to c6 I'm going to type a comma and then one two three so you can see this emerging up here there's the column index I'm going to leave the last argument off it's the default close parenthesis and enter so now that looks like it's working control shift four I change this to zero eight thousand to eleven thousand all right and so that's from a different sheet the only difference is you have one extra click you click on the actual sheet now let's do and you can see down here I have this workbook so we can go back and forth equals vlookup I'm going to say lookup that comma table well it's in a different workbook so I come down here and click I highlight the table now notice by default it has these dollar signs which means it's absolute just like a defined name I'm going to type a comma you can see it start to emerge up here three closed parenthesis now let's take a look at this one this one puts the in square brackets the name of that workbook when you close it it will have the file path name there now if you're doing a workbook reference you better keep the workbooks together or at least in the same place and don't move them because they're communicating I'm going to ctrl s to save this I'm going to close this one right here and now when I open up this you can see the whole file path name alright now let's go over and talk about the next topic full record retrieval alright so we have a database here and I want to do data validation and show my the idea and then extract any idea right here I literally want to extract the entire record and want to see four ways to do this now I'm going to do alt DL AB L tab and highlight click okay now watch this I'm going to copy that cell and control V and control V and control V so all I did was copy and sure enough the data validation came so I'm going to select this which is this record right here now the the vlookup is going to need to know in its formula for last name it needs to know the second column for first name it needs to know it needs to know that's in the third column fourth fifth now there's a few ways we can do it you can literally put the numbers right here to tab three tab for tab five totally easy way to do it so equals vlookup I'm going to say lookup this now we're copying this formula to the side so we're going to have to lock it now if you hit the f4 key it locks your cell reference no there's two dollar signs a means the column reference a is locked 14 means the row reference 14 is locked now that'll work here so I'm just going to because we want it cut we want it locked as we move later we'll talk about situations where you want one lock but not the other the table now we use define names just a little while ago but certainly you don't have to you can just hit the f4 key because we're going to copy this fact let me leave it like that and we'll just make an error and come back and fix it now the column I'm simply going to click right there comment and this is an exact match so I'm going to put 0 all right now let me drag this over one I pointed to that little fill handle in the lower right hand corner and that crosshair or angry rabbit allows you to click and drag now you can see what happened here the formula totally obeyed us right it we didn't lock it in anyway so the green box moved called a relative cell reference so let's come back over here and hit I'm going to highlight this whole thing and hit the f4 key that b12 or two cells above I want to be relative cell reference so when I drag this over I come over here and I can see it got it exactly right that's locked there the green ones locked there and that one is moving as a relative cell reference all right now there are other ways to do this sometimes you can't have you know or column index number up here so we're going to look at the columns function equals columns columns all it does is it looks at a range and tells you how many columns there are so I'm going to type this one out I'm going to type dollar sign and I'm sitting in cell be 18 so I'm going to type be 18 colon be 18 now this construction here is an expandable range it means it will expand as I copy to the side notice the B if it's a relative cell reference when I copy it over it'll move to C whereas the dollar signs in front of the B it says you are locked so right now it's from B to B how many columns are there one but when it goes to B to C it'll give us two so column says how many columns now the cool thing about this is it is it'll give us 1 2 3 as we copy to the side that's not exactly what we want so I'm going to add one more in and that will give us exactly what we want that is going to be our the just part of our phones and that's going to be the column index number so now we build our vlookup VL tab I'm going to click on that f4 to lock it that's the lookup value comma our table f4 to lock it comma and there's the column index number control-enter and so we have some problem here forgot to put comma zero and then copy it over all right and so we can change this and it will update all right now sometimes you don't want your data shown or the the fields extracted horizontally you want it vertically so we can simply do that with equals VL select that f4 comma the table f4 comma and then instead of columns you guessed it we use rows I'm sitting in B 22 so guess what this time we don't lock the B we lock the 22 right that will give us how many rows 22 to 22 is one we want to so we start with plus one now there are other ways I'm going to put a 0 and there's other ways you could use the row function but the rows is more robust because that whole the information which is there going to give us 1 2 3 is from that cell so if you ever to insert any other rows or columns this will not give you the incorrect number whereas if you use just row you might have a formula error and extract the incorrect record all right so now I can copy this down it looks like I'm extracting the correct one still one other way is to use the match function now right here we have field names and if I ask you which position is the word last one - oh how about this which position is the email 1 2 3 4 so we can use the match function now the match function is a lookup function we're literally going to look up this field name right here the match function will look up last but won't return a thing from the table it will return the relative position so I can use the match function lookup function comma there's a lookup value comma within lookup array I'm simply going to highlight this now I'm going to have to hit the f4 key and watch this when I hit f4 it not only locks it but it jumps the screen back in which is kind of nice , I am looking up a word and that so I'm going to use exact match notice in later videos later this week we'll talk about match match has a few more options than vlookup the 1 and the 0 or exactly like vlookup this one will be a little bit different now remember vlookup I mean some matches looking up the word last in that range and it tells us it's in the second position is that not totally cool so now we can simply use that match inside of our vlookup I'm going to look up this f4 comma the table array I probably should have named it f4 comma and there's our column index the match is delivering the column index number comma 0 and so I can select and there we go all right so that's four different ways to extract actual full records let's go look up add another common vlookup situation now it looks now you here's the situation sometimes you have two or more lookup values the world you're going to deal with that right we don't have a unique identifier in the first column we have Joe Joe Joe so if we looked up Joe it would only get the first one any time you're doing an exact match and you give lookup if you look up some value and there's duplicates it'll just return the first one now we really need Joe Washington Joe Oregon Joe California so the easiest way to do this is to add a extra column to the to the left so it becomes the new first column and join these two it's easy enough to do I'm going to say relative cell reference and I'm use seven that's the ampersand that is the join symbol and then I'm going to put some character in double quotes like a dash or a vertical bar or something like that and double quote ampersand and then this so what I've done is created a unique identifier now there'll be no duplicates in this first column sales rep some symbol state ctrl enter and then double click and send it down I'm taking my angry rabbit and double clicking now we have a unique identifier now since we have two different cells inputs over here we need to join these two things inside of our vlookup so watch this if you look up and if you look up well now our whole table is going to be this so that's the first column that ampersand double quote vertical bar or double quote ampersand and this so if we were to highlight this in side the formula you could use your f9 key to evaluate and you can see now we have from two input cells to lookup value we have a single lookup value I'm going to control Z I don't want to hard-code that in comma the table comma the index number I want ID which is one two three four and I'm doing an exact match alright so that is a great way to do that if I take Jo California now you could mix this up right and then get an A and then you'd want to do something like either the if that we saw or in 2007 or later you do the if air and then some message like choose again or something more informative than that I can't even spell f7 look I spelled it so wrong it hardly even knows choose again alright so then we get California there it is now what if you couldn't add this occasionally that will happen you know this is this format here is going to get a little fancy here it's going to be what's called an array formula that's going to require a special keystroke normal formulas you just hit Enter or ctrl enter but here we're going to have to use control shift enter now here the first example is really the best way because you just add this extra column you have your unique identifier but if you can't do that for some reason inside the formula we are going to have to create these two columns join together not only that but once we join them together or then inside the formula are going to have to say hey this is the first column and this is going to be the second column and we're going to use the choose function to do that now this is going to get a little fancy here if you look up we're still going to look up this there's our lookup value comma our table we're going to have to as I said make make this table actually inside the formula now we're going to use the choose function because we're going to in two separate steps we're going to have to join these two columns in one step and then in another step we're going to have to join this new single join column with this one so choose we have two steps the choose function is a lookup function and we'll talk about this one later also in more detail but usually you give it a number like one two three four and you just type in all of the things that potentially can be delivered to the formula of the cell but I want two things simultaneously so I'm going to put curly bracket 1 comma 2 and curly bracket now that's an irate constant and what it's going to tell the chooses take the first column which we're going to create in just a moment and mash it together with the second column that'll be this one simultaneously all right the value one to choose you actually type in all your values well I'm going to take this whole column and join it with this whole other column now let's check this out right in the middle of a formula you can highlight this and hit the f9 key and sure enough you can see there's oh just like it looks like here that semicolon in erase syntax means go to the next row so sure enough it's Jo Washington Jo Oregon Joe Kelly California Sioux Arizona ctrl Z that's the first column member simultaneously get 1 and 2 and put them together comma and then the value the second value is going to be this that is fancy that choose they're literally made up a lookup table that didn't exist in the spreadsheet so watch this if I highlight this entire thing and hit the f9 key you got to be kidding Joe Washington comma means go over to next column that thing which is that ID semicolon means go down to the next row and then it's this one this one absolutely amazing control Z alright so that's the table array comma column index we have one two and I'm returning the second right because our choose mash these together for the first one and then have this for the second comma and we're doing exact 0 now if you hit enter it's going to give you na because array formulas require control shift enter so if you enter an array formula you can actually get a value or an na in this case and the array part of this is the definition of an array formula is you do some operation on more than one cell and snow tiss the whole column and then this is the operation join with another column forget it that's an array fulness you have to control shift and enter there we go so now if I change this to Jo porygon so we're getting Jo organ the correct lookup that's pretty fancy again that's probably the easier one and I probably want to put the same affair there for that one now there's some other situations you run into a vlookup I'm going to click on this partial text sheet occasionally you get a situation where you have some ID so you get a data dump for somewhere else and they use a different ID than you do but the first column of your table is contained within their larger ID so in this case what we want is we want to find just the word before the - in each one of these as our lookup value and then run the rest of the vlookup the way we've been doing now we're going to see three examples here if our table had the product name here it has the ID in the middle and here it has the stuff at the end right now the trick here is we're going to use the left function and the left function straightforward you say from the left how many characters you want but the trick is they're very a bowl length right this is 1 2 3 4 5 6 this is 7 and this is 4 so we're going to also use the search function to search for that - assert the search function can say at what position a particular character is so if I do search I can say find what text - there's actually two functions that do the search and find find is case-sensitive search is not but we don't need to worry about the - because it's not key sensitive so I'm going to say find that text within here now that'll tell me 7 right because it's the 7th position but it means the 7th we don't want that we want minus 1 right 6 4 7 exactly how many characters from the left we want so now we go left the text is that comma and that's how many from the left double click and send it down that's just the lookup value so now I say vlookup there's the lookup value comma the table comma and I'm extracting the price from the second column and I'm doing exact match another situation is when we have our the thing we want to look up in the middle so here there's two things the we're still going to have to run the search to find that and then add one to say where to start but the cool thing about this is they're always going to be three so we're looking at the actual ID here recognizing the pattern the pattern is the dash is a variable length so we're going to use search and then add one but since the the number is always three characters we can hard code that into our formula and when you use the mid function just take something from the middle left take something from the left mid take something from the middle right here's the text comma the starting position we're going to use search search for what that dash with text extracted and formulas you always have to look at the pattern because they're always different right sometimes you can hard code it in sometimes you have to use search or something else all right so we're going to search for that that search will give us seven we need to add one because the starting position for us is going to be eight if I highlight and hit f9 that is eight control-z come on the number of characters three now there's going to be something interesting here but let's just say we didn't notice that you can already tell if you can see the difference between these two but let's just pretend what we don't we're going to do vlookup that's the lookup value comma within this table comma second column Alma 0n a well right off the bat this is aligned to the left this is aligned to the right so Excel think this is by default a number this is text you cannot match text against a number so we can trick it value that's this mid thing right here and actually if you highlight it and hit the f9 key you can see it's in double quotes that means Excel thinks it's text so we'll trick it any operation on a number stored as text converts it back to a number I'm adding 0 so now just because I added the 0 to it I hit f9 there's the number ctrl Z well ctrl Z ctrl enter and double click and send it down and finally we have the right now we can notice a pattern here 1 2 3 4 1 2 3 4 1 2 3 4 it's always going to be 4 so we simply say write of this comma 4 so that's the easiest of all of them but extracting partial text always means you recognize the pattern you better know you know be sure about whatever pattern you recognize it's always for here we're positive that'll work just fine it's matching text against text so we simply look use that as our lookup value comma within this table second column exact match all right so that's a three partial text situations let's go over to the next sheet trim here's some another situation that happens sometimes and you know sometimes it's hard to figure this one out but anytime you get a situation where you have lookup and your you have a table , we're going to extract price one two three four and we're doing exact match so I can see right with my eyes quad right anytime I see this the very first thing I check for because it can be caused by lots of things is I check for extra spaces and sure enough there's an extra space if I backspace that works perfect now on a huge data set you may not want or you you know from various reasons you can't or its data dumped or something like that so we need to get rid of those extra spaces no problem now a lot of people think that trim function is great because it can give you a haircut no that's not what it does other people say no trim is great because it puts you on a diet no it doesn't do that either all it does is it removes all extraneous spaces except for a single space between words so trim bone if I highlight this and hit the f9 key you can see that extra space control-z if I highlight this trim and hit the f9 key gone the space is gone ctrl Z ctrl enter now there could be other things causing problems but that is one quick easy fix and it looks like it's getting them all correct I hope no it's not look at that it's only by accident I forgot to lock so the rule of thumb is when you're entering a form and copying it down you go to the last one and you hit the f2 key and you got to verify using this great range finder that means the color coding that's wrong go to the top highlight and f4 to lock it control enter double click and send it down okay now the opposite can happen if we scroll down here we could have spaces in the actual lookup table and so instead of doing trim on the lookup valuable to trim on the table now we're going to be doing an operation on an array here so we're going to have to use control shift enter else Oh vlookup I want to look up this comma I'm just going to pretend that I don't know f4 to lock it comma column index one two three four that has the price we're trying to retrieve comm and we're doing an exact match control-enter sewed n/a we double click and send it down and we do some investigating now in a small table like this certainly is easy enough to just get rid of the spaces but for some reason you couldn't we'll just do trim on the table that will cause yeah we'll do trim on the table the thing about trim function just like left right mid trim all of these functions deliver text so let's just see if this works control let's just all I did was he enter that value error you'll either see a value error or an n/a that says you didn't enter your array formula with the correct keystrokes on the whole control shift and enter immediately when you see a number aligned to the left you suspect that there is some trouble here and what's happening is trim is delivering text so and we can highlight this actually in C f9 and sure enough got rid of all the spaces really in just the first one but now the number is txt no problem just as we did a little while ago earlier in the video we can do any operation on a number stored as text and it converts it back to a number as a number I'm simply going to add 0 control shift enter double click and send it down so there we did trim on the actual table all right let's go look at another example three tables so in this situation lwg and actually I see that I deleted something here but ok so the situation is we have to look something up unit Sol but we have different products and each different product has different commission rates so what are we going to do here well first we have to have some trigger that tells the formula which table to go to and there's there's a few ways we could do this now I just put the ABC up here just so we know which table is which since we have to choose amongst three different tables let's use the choose function now earlier in this video we saw the choose function and our index number we actually gave it a 1 and a 2 so it simultaneously chose both of them but here we're going to use choose in the traditional sense we're either going to put a 1 for ABC a 2 for EDR or a 3 for EDS now how we're going to do that when the input that we have and the trigger is the actual product name right so I'm actually going to click escape and come down here and type a 3rd or 4th table 1 2 3 all this is is we have our product name is the first column here this is going to be the trigger for any particular calculation I need which table from ABC we're going to need to have a 1 there inside of the choose which will say go here alright so equals choose now the index number let me just skip over that for a second the value is going to be easy I'm going to highlight the first table and f4 comma the second table and f4 to lock it , and then the third table and f4 because what does choose do chooses a lookup function where you give it some number and then you just put all of the values actually in the formula but look at this in this case we're choosing columns the order is important ABC is 1 EDR is 2 and eds is 3 that's why we have this table here and guess what for the index number we are going to do a vlookup inside of the choose alright this is just to get the right table right lookup value that right there relative cell reference comma the table and f4 right now ABC will look up vlookup will take this deliver a 1 inside that right comma but back to the vlookup we're looking up ABC we need to say which column here has the table number it's the second one so I'm gonna type of 2 sometimes when you're doing big formulas these screen tips really help that means I'm inside the choose column index comma and I need exact match so I'm going to put a 0 close parenthesis now watch this once I close parenthesis now I'm back to the choose screen tip and then there I have my range now if I highlight this remember choose is just deciding which table and I hit f9 sure enough it got this first one right comma 1 percent semicolon means goes down in the next row 100 comma 2 percent there it is right there semicolon means go down to the next row so it chose just one amongst those three tables now that whole thing right there is going to go inside the vlookup where the lookup table goes so you're ready the lookup what's the lookup value finally we're down to the unit's right because that's what we're trying to do is get a rate so I'm going to select relative cell reference there comma that table array that gigantic thing right there I come to the end I'm going to type a comma column index is second that's the second in each one of these and this is approximate match so when I type a two and a comma I don't really need that the default is approximate match them in the back space and that's done with our formula I simply put a close parenthesis and ctrl enter no control shift enter there's no operation on an array there double click and send it down so ets 188 EDS the way approximate works is it bumps into the first number bigger jumps back the next row and delivers the two here for ABC 493 that should be 4% so sure enough it's working let's try EDR let's put something like 600 here and then that should be 6% which is correct see all right so that's one way to look up multiple tables let's go see another example I clicked on the two way sheet now here we're going to do two-way lookup we actually already saw an example of this but we want to explicitly do it here because vlookup is often thought of as just being able to do one-way lookup meaning you just look up a row number but here we want to explicitly do two-way look up we have some income and allowances so if we have 200 income allowances three we need the intersection of that three and that approximate match there no problem we'll use vlookup to determine the row and match to determine the column equals vlookup the lookup value for the row is the 200 income comma the table make sure to get those right there comma and then the column index will just use match matches a lookup function that allows us to look up a value and it tells us the relative position so lookup array it's going to be those right there comma 0 close parentheses so the match will deliver a one two three four five the lookup range or them the match type it's approximate right because this vlookup is looking here it needs to bump into the first bigger one and jump back so we leave it off the default is approximate all right and so now if I change this to 1900 then we have three and six bucks so that's two way look up using match and vlookup and finally the last example this will be a doozy we want to see how to for some situations this is actually a tax example but you could have variable tax rates variable commission rates and how we solve this problem is similar for all of these now the trick to doing variable rates is to build a smart table and most tax tables come already kind of completed for you because here's the deer our taxable earnings or 2000 but the first one thousand three and thirteen dollars is zero the difference between these two will be taxed at ten anything above that between these two values will be fifteen between these two be 25 so it really is a multi-step complicated calculation here but the trick is is you you calculate all the taxes from the previous brackets actually if you do taxes the table literally when you download it looks just like this and here's how it works so I'm going to start off with 7,000 right so what it does is it has to be over this but not over this so we're on this row right here and here's the rule it says take 700 $12.40 that's all the taxes calculated from previous brackets plus 25 percent of the excess over six thousand three hundred and four because that number right there represents all of the taxes that have already been taxed from earlier brackets so here's the deal let's just see if we can do this right here off to the side if you are not automating it here's how you do it it's a equals well the rule says 7 or 12 so I'm going to take that plus any excess well this is the amount that's already been taxed so I'm going to take my 7000 minus this and then times the tax rate and so that gives us our 8 8 640 so really what we did here if we're going to automate this we can literally take this form that is going to be a vlookup because there is the number to look up approximate match I actually added tax tables look like this but we need the smallest number in the category to occur in the first column so I just added an extra columnist at 6000 oh four plus one penny because notice it says over that amount so seven thousand we are going to look up and that will determine this row but look once we know the row that East 7 right there is which column one two three four five this one is one two and this one is one two three four that means there's going to be a vlookup there with column five a vlookup there with column two and a vlookup there with column four watch as I'm going to cheat and just even put this here that'll be our prompt so I'm going to double click that and I'm going to type vlookup what's the lookup value every single time it's going to be that the table array I'm going to highlight it yeah we can just do the whole well we're never going to use that last one right there okay so that's the table array comma and this one gets one two three four five because we need that seven twelve so five and the last argument we don't need because it's approximate Matt so we leave it off now I'm going to copy this control so you let's just see if that gives us the right number okay all right so we have vlookup that is substituted for that one cell reference we're always going to need that seven thousand but we're going to need another vlookup right there now I copied this right there in edit mode so I'm going to control V and what's the only difference I need that the amount that's been taxed from earlier brackets on this line which is column two so I simply change it to two and then finally I need one two three four the tax rate so I control V and change it to four now the nice thing about this formula R not this one is this one when I change the number right here will not update that's manual this is automatic so if I change this to two thousand five hundred boom one one hundred forty one dollars and eighty cents but this one's totally incorrect you'd have to redo it each time manually wow that was a lot about vlookup 25 examples you know but I've already thought of one other example I'd like to do so I'm going to add a twenty six one I'm going to click on this extra sheet here and I'm going to call this dynamic so we have a product and we have a price and the idea for this is that oftentimes your tables change over time so I'm going to have my belen and the price of 25 or whatever it is and lo to Boomerang and that's 26 will make a little small example here the idea is I want it expandable an expandable range I'm going to click in one cell and in 2003 2007 and 10 you can the table or list feature in 2003 it's ctrl L in 2007 and 10 its ctrl T when I click OK if formats it but anytime I add a new record down here any formula or chart or whatever that's looking at it will automatically expand so here I'm going to have my product online price type felon and then I'm going to equals vlookup there's the lookup value comma and watch this when I highlight that it says a2 to be 3 okay but notice B 3 comma I'm retrieving something from the second column comma and 0 no problem now let's come down here and tables you can either click in the last cell and hit tab I'm going to ctrl 0 you can just start typing quad 36 and now I'm going to come up here and quad ensure I've got 36 and now let's look at this in edit mode oh it went to be 4 so it knows so that's an example of when you have an expandable range let's do alt DL tab L tab then I'm going to highlight this range right here a 2 2 a 4 and click ok so now right now it's got quad when I come here and I type majestic beaut the 35 I'm going to come down here ah ha ha look at that so the data validation now has expanded and sure enough the price has to all right 26 examples of vlookup we'll see you next video and there'll be more videos throughout this week with other lookup functions and other lookup tricks see you next video

Channel: ExcelIsFun

Views: 2,512,228

Rating: 4.9065213 out of 5

Keywords: Excel, 2003, 2007, 2010, VLOOKUP, lookup, HLOOKUP, COLUMNS, ROWS, MATCH, CHOOSE, LEFT, SEARCH, MID, RIGHT, TRIM, Tax, Shark Week, VLOOKUP SHark Week, Mike Girvin, excelisfun, Highline Community College, Defined Name VLOOKUP, Slaying Excel Dragons, Lookup on different sheet, Lookup different workbook, Two lookup values, two lookup columns, Join two lookup values, Partial Text lookup, lookup left, CHOOSE function, How To Use Excel VLOOKUP Function, VLOOKUP basics, Excel VLOOKUP Function

Id: -hJxIMBbmZY

Length: 68min 3sec (4083 seconds)

Published: Mon Mar 26 2012