Mysteries of VLOOKUP Function Revealed! 15 Amazing Examples! (Excel Magic Trick 1514)

[Music] welcome to the mysteries of vlookup function revealed yes 14 amazing examples to help you to become a vlookup master and if you want to follow along with the examples in the video you can download this excel file in the link below the video if you like what you see in this video click that subscribe button and the bell icon to get notified about new videos here's the 14 examples we're gonna go from the basics to advanced and this list of 14 vlookup examples this comes from over two decades of answering questions about vlookup and in this video I try to put it all together into 14 topics that'll make you a vlookup master now this will be a long video with lots of topics so guess what below the video there's a time hyperlink table of contents you can click on anytime and it will jump automatically to that part of the video now we're gonna start it off with the basics talk about why vlookup is everywhere and then look at the difference between exact match lookup and approximate match lookup then we'll go on to more advanced topics we're gonna start it off by going to sheet 1 now what do I mean by vlookup is everywhere well looking things up in lookup tables is a common task in business accounting and many other professions and almost all lookup tables are vertical notice V is the first letter they could have just called it lookup and in fact there is a straight lookup function that was the original function all the way back in visi calc back in 1980 but soon after because most tables were vertical in came the V lookup function so V look and vertical look-up tables are everywhere let's look at some examples and we'll see that they are incredibly common if I ask you what is the price for the quad product well if you knew that this was the lookup value and we had a lookup table you take the lookup value and in the first vertical column you try to find a match once you find a match you know this is the correct row and there it is 43 95 is what we're trying to return to whatever formula we're creating as a second example if your sales are 36 500 what is the Commission bonus percentage that you earned well this is the lookup value and we find the correct category $30,000 or more all the way up to but not including 40,000 that value fits in this category so 4% is the value that we are going to return next example Human Resources if I ask you what Chuck's house zip code is or you'd find a match in the first column you know this is the correct row and we would return the zip code 9 8 1 1 1 here's another lookup situation if your income is 3000 what is your tax here's the correct category for $3,000 lookup value so the tax is 60 bucks how about this one what region does miles Josephina represent this is a typical lookup situation we have a lookup value we find a match in the first column we know the correct row so we return Southwest how about your sales of 6000 what's the earn commission well here's the correct row there's the return value $200 and as a final example what if your sales are 375 what is the correct sales category well this is the correct row for the look of value 375 and that's the category average not below par or excellent just average now you might be asking if you're learning vlookup for the first time I'm going to use control home to jump back to cell a1 there seems to be two different types of lookup and in fact there are and the amazing thing about the vlookup function is we can do exact match or approximate match now what is exact match that's when we have a lookup value and we're trying to find exactly the characters qu ad we have to find a match with only those four characters that's called exact match down here if we searched for 36 500 exactly there is no exact match but luckily if we tell vlookup this is the lookup value but we want to do a proximate match lookup vlookup will race through until it finds the first bigger number and then it will know to jump back and that's the right category so every single time we do vlookup we have to ask are we doing exact match or are we doing approximate match all right we want to go over to sheet 2 and look at our first example in this cell we need to look up the price for the quad boomerang in this lookup table that means we're trying to match exactly that in the first vertical column that means we're gonna do exact match lookup now for both example 2 & 3 I actually have a bunch of notes in the yellow cells up here that describe the exact and approximate match so you can look at that later now here we go we're gonna do this formula we're gonna look up the price equal sign starts all formulas and watch what happens when we type a V a list of the possible functions that start with V pop up when we type V L will always get vlookup so if you're doing vlookup a lot its equal sign VL and the tab key to accept the vlookup from the blue highlighted option so tab now there's one two three four different arguments we have to put into vlookup now these arguments work just like we do it if we were looking something up manually what does lookup value mean well just like we have to look up quad and remember it to try and match it in the first column so does vlookup so for lookup value I put the item we're trying to look up and match now notice something about this screen tip it tells us this is the vlookup function and then it tells us each one of the arguments it needs now we just put in lookup value its bold there that's vlookup saying that's what I need once you enter that argument you have to type a comma so when I type a comma now the next argument is bold now this argument is called table array this is where we highlight the entire lookup table now I always remember that the lookup table goes here because it has the word table so I simply click to highlight the table now when you're putting a table array in for the vlookup function you do not highlight the field names just the first vertical column with the items we're trying to match and then any subsequent columns that have things you want to go and get and bring back to the cell alright so we have our table we type a comma column index number is the next argument now we know as humans looking this up manually to just read the word price and we know that we have to get the price from this column but vlookup needs the column index number and what that means is you have to count on your fingers 1 2 3 this is the third column in this lookup table because it has the item we want to go and get and bring back to the cell we simply type a 3 here now if I type a 2 it would be getting supplier name backspace 3 when I type a 3 now if you look up we'll know to get whatever's in the third column in our case price now we type a comma range lookup oh here it is this is our option approximate match or exact match this is where we tell vlookup which of the two types of lookup to do now we can simply arrow down if we put true it does approximate match if we put false vlookup is instructed to do exact match now that's what we want here so watch this I can simply hit tab now because false is in range lookup vlookup notes to do exact match but now I'm going to delete this because there's something very important there are other lookup functions and in fact later in this video we will see the match function and the match function has the same two options but for approximate match we have to put a 1 and for exact match we have to put a 0 so what I do is instead of putting true and false I put a 1 and 0 then I know the universal code for approximate match or exact match now by all means you're welcome to put a false there that's why that drop-down is there but for me I'm putting a 0 0 means false 1 or true means approximate match now in all of the notes right down here and up there it tells you you can put either/or alright now watch this closed parenthesis and if I click back inside of the parenthesis the screen tip pops up I can actually click on each part if I needed to edit it but look at this we have a lookup value that's the item it's going to try and match in the first column there's the lookup table the column index we're getting price from the third and there's our exact match so when I hit enter that is so cool that formula looked up quad and returned 40 395 now if I type V rang and hit enter that is Magic V lookup totally look this up found a match raced over to the third column and went and got 1895 and brought it back to the cell now think about that for us using Excel and vlookup we want to have a firm idea in our brain of what vlookup does and what does it do yes it looks something up and finds a match but fundamentally what does it do it actually goes and gets something and brain it back to the cell so now if I type bell in enter it did its job it went and got something and brought it back to the cell now I'm gonna try Carlota and when I hit enter in a well that is an error but it's actually a polite error that means not available anytime you get an na when you're doing an exact match it means that thing is not in the first column now click in the cell and hit f2 and now we see one of the most common errors with vlookup extra spaces remember what we said exact match looks for all of the characters and you better believe that when you hit the spacebar that is a character it cannot find Carlota space but when I backspace and hit enter sure enough now it's doing its job it's going and getting the price for Carlota now here's what you don't want to do oftentimes people will learn the if function before they learn vlookup you don't ever want to do this it takes too long to type the formula out and it's totally inefficient it'll give you the right answer but it's just flat-out too hard all right so we did exact match with a 0 in the 4th argument now let's go over to the sheet 3 now on this sheet our goal is to figure out the commission rate that rod earned rod had sales of seventeen thousand three hundred and eighty two now we're gonna create the vlookup formula here this is our lookup table what we don't want to do is f2 I definitely don't want to do that it's so complicated it's actually amazing how many people create formulas like this because they don't know the more simple vlookup solution so no problem here we're going to use vlookup to look up the sales doing an approximate match lookup now I created this third column here so as we're learning vlookup we do not get confused with what that lower limit in the first column means now for 2% to be earned that means the salesperson had exactly 2,000 all the way up to but not including the next lower limit I listed it explicitly here to earn 2% you have to sell 20,000 or more all the way up to but not including 30,000 so if we're gonna do a proximate match with this number on this table we're actually not going to use this we're only going to use these first two columns now notice this first column is sorted smallest to biggest approximate match vlookup will not work unless the first column is sorted smallest to biggest well the good news is most tax tables Commission tables tables for looking up sizes and construction they always have a first column that sorted smallest to biggest now rare cases they'd have it from biggest to smallest but then you have to do something besides vlookup but because most lookup tables are sorted smallest to biggest in the first column they program vlookup that way now there's an easy way to remember how vlookup takes a value and finds a match to determine the correct row and here it is think of it this way vlookup will take that number and race through the first column until it finds the first number bigger and then it knows to jump back to the previous row now technically that's not how it works technically there's a computer process called a binary search that makes a proximate match lookup really fast but for us we're always gonna say hey there's the value race down the first column until it bumps into the first bigger one and then jump back of course thinking about it that way also helps us remember that if it's dependent on bumping into the first one bigger that first column better be sorted smallest to biggest all right let's see how to do approximate match lookup equals VL tab lookup value that's the thing vlookup needs to look up and remember comma the table the first two columns comma column index number that call remembers that we need to give vlookup the column number of the column that has the item I want to return since bonus Commission is what I want to get in return 1 2 I simply type a2 now I type a comma here is where we put the option for approximate match now you can hit tab and put true you can put a 1 but guess what when they program vlookup since taxes and commissions and things like that are so common they made this the default so if you completely leave this argument out vlookup will know to do approximate match lookup so watch this backspace do not accidentally leave a comma backspace when you're doing an approximate match lookup don't even put that in as soon as you put the column index you simply close parentheses so approximate match lookup is a little bit easier than exact match except for of course you've got to remember to sort this smallest to biggest and would you like to do that one or this one I'll take that one every day now let's do some testing to see how approximate match vlookup works let's say our sales were really 37,000 when I hit enter what did the vlookup formula do it took that number raced through until it found the first one bigger and jump back then it went over to the second column got that and brought it back to the cell what happens if rod went crazy $90,000 in sales well what did vlookup do it took it raced through oh and it never found one bigger so it always will take the last one if it can't find anything bigger so of course it returns 7.5% now what if this was the worst day in rod sales history not only did he not have any sales when they zeroed everything out the credits outweighed the sales so there was a negative number oh na and the reason why is when we're doing vlookup approximate match if the lookup value is less than the first possible number in the first column it gives you an n/a that's why when you're designing this table you have to put some number here that's smaller than any possibility now sometimes when sales are possibly negative they just put a really big negative number like something like that and this will take care of any negative numbers I'm going to ctrl Z to undo because I'm going to leave it as 0 and then I'm going to ctrl Z to undo and bring it back to 90,000 all right so that's approximate match lookup now we want to go talk about some really cool things that we can do to make vlookup even better I'm gonna go over to the sheet 4 and we're going to go back to exact match now watch this once you get the hang of it I need to look up the price for whatever product I put there so equals VL tab and guess what instead of taking my mouse and clicking I'm going to use my arrow key to arrow over and get that lookup value comma and now we select the lookup table comma the column index is 3 that's the third column comma 0 for exact match closed parenthesis and when I hit enter and a as soon as you see that it means whatever's in that cell is not in this column now it might mean that you didn't highlight the right table right but it still means that value is not in whatever the first column is now I'm going to control Z that undo actually undid my mistake so what's going on here well just as we saw earlier in the video f2 there's that darn space well we humans make mistakes all the time I am gonna backspace and hit enter that does fix it but there's a much better way something called data validation list well if we're using vlookup and the lookup table is here there's a great way to simply add a drop-down list that we can select from that will only select items from the first column so you're ready I'm going to select the cell go up to the data ribbon tab now I'm in the latest version so my icons may look a little bit different but you want to find the data tools group and the data validation button I'm simply gonna click the data validation button data validation it's gonna do exactly what the name means whatever data I put in there is going to be validated now there's a bunch of options the default is any value which means anytime you open up any spreadsheet you can put anything you want in a Cell I'm going to click the drop-down and there's a bunch of cool options you can limit it to whole numbers decimals date/time text length and all sorts of cool options but the one we want is list as soon as we select list it says what is your source now for source it has to be a one-way range meaning either vertical or to the side now for us using vlookup it's always going to be vertical because of course the first column is vertical do not highlight the rest of the table just the first column from now on there'll be an N seller op down and that cell will not allow anything unless it's already in the vlookup table so when I click OK click in the cell and now there's a drop down that is true Excel magic data validation list with vlookup now let's go look at our fifth example I'm going to click on the sheet five here we have a data set and we imported this data set this is a scientific experiment where we're tracking certain bats these are the bad IDs here's the x of the sightings and for our final report we do not want bad ID we actually want to look up for each row looking up bad ID and returning the full bat name so no problem look up can do this and this will be an exact match because we're trying to find exactly whatever these characters are in the first column alright so in C 5 equals VL tab lookup value I'm going to use my left arrow comma the table and I'll highlight the entire table comma column index number 1 2 3 and don't get confused with the letters up at the top sometimes when people are learning vlookup they might think because G is the seventh column we should put a 7 but it's always from the point of view of the table 1 2 3 we're trying to get something from the third column so 4 column index I type a 3 comma 0 because we're doing exact match closed parenthesis now if I hit enter that will put the formula in the cell and push the cursor down well if I want to copy that formula I then have to select the cell again and then copy it so instead of using enter I'm going to use ctrl enter ctrl enter simply puts the thing in this cell and keeps the cell selected now I want to copy this down and that little green box in the lower right hand corner is called the fill handle and by the way that cursor right there is called the selection cursor but watch what happens when I move my selection cursor over that fill handle oh it turns to a crosshair cursor or I like to call it an angry rabbit cursor now the cool thing about the angry rabbit cursor is you can click and drag and copy the formula down uh-oh and A's what in the world is going on well anytime you copy a formula down a column you always want to go to the last cell and we're gonna put it in edit mode using the f2 key the reason that we go to the last cell and check it is because we want to verify that all the cell references are pointing in the correct location well we can already see that the blue one is pointing in the correct location but not orange one why is the table down there it should be up here well that means we have to learn the difference between a relative cell reference and an absolute cell reference now I'm going to go up to the top and hit the f2 key notice the blue one is looking one cell to my left and as I copy the formula down notice the blue cell reference is actually moving as the formulas being copied but watch the orange one enter or enter it's moving also we do not want that to move if from the point of view of the formula we want the cell reference to always look relatively one cell to the left that's a relative cell reference but we don't want this to be a relative cell reference so we're actually gonna have to go back up to the first cell and properly lock it as an absolute cell reference so we're gonna highlight the range and I'm gonna use the Delete key and we're gonna create the formula again equals VL tab left arrow to get my lookup value comma the table and after we highlight our lookup table we need to convert it from a relative range of cells to an absolute range of cells and the way we do that on a PC is with the f4 key if you're on a Mac you use command T look what happened those dollar signs in front of the column references and the row references mean that as we copy that it will be locked now we have our absolute cell reference for our lookup table now we finish the formula comma 3 because we're trying to get bat name comma 0 close parentheses control enter and we're gonna do one better this time instead of clicking with our angry rabbit and dragging we're gonna double click and it automatically copies it down it knows to stop here because there's no more records now anytime we copy a formula down a column you have to go to the last cell in hit f2 we're verifying that the cell references are pointing in the correct location and that is beautiful relative cell reference absolute cell reference exactly what we wanted now we have our data set and we can create whatever report we want using x and the full bat name now we want to go on to our next example example 6 and we want to see how to create a dynamic lookup table and what that means is right now we have a lookup table we have data validation drop down all the way down to Carlota and our lookup table arrays looking at the correct lookup table but what if we added new records or deleted some of these with our formula and data validation update not as it sits now the way we can create a dynamic that means we can expand or retract the rows in our lookup table is by using the excel table feature now in order to convert this to an excel table you have to make sure that you have a proper data set the column header names or field names have to be in the first row and then the records or in our case product supplier and price has to be in each row and you have to have empty cells all the way around your proper data set if those conditions are met it's easy to convert it to an Excel table you select a single cell go up to insert table group and there it is the table button or we can simply use the keyboard ctrl T now make sure that this is checked we have headers click OK now it adds some formatting and very conveniently if you want it adds sort and filter buttons we don't really care about any of that we just want the behind-the-scenes dynamic expandable contractable ranges now before we can use this in our formula we have to make sure and go up to the context sensitive table tools ribbon tab notice if I click on the side it goes away click in the table table tools and you got to go over to properties name this table we're gonna call it something like product lookup and when I hit enter we have registered that name for this table and we have data validation that was pointing towards the first column if I click the drop down oh it says Carlota but watch this we're gonna add a new record now you can simply click in the cell directly below the last record and we're gonna type a new product sunshine now as soon as I hit tab watch what happens to the corner of this table tab it totally expands already before we even complete the record when I look at the data validation list it's already acknowledging an expandable range now I'm going to complete this that's channel craft tab and the price is 1995 now if I hit tab right here it would add a new record and I don't want that so I'm simply gonna hit enter now let's create our lookup formula using that table name equals VL tab left arrow to get our product name comma and the table array I'm going to show you three ways to put the table name in the easy way is simply to click and drag and because it's an Excel table it automatically puts it in another way if you have a big table is watch this your cursor that is a diagonally pointing black arrow for an Excel table when you click it tells the formula to automatically put the table name in still another way if the table is far away you can simply type and I'm gonna down arrow and in my drop-down for functions of course table names will appear also tab now we can finish our formula comma three comma zero close parenthesis and enter now let's see if this works I'm gonna come and click the drop down for sunshine and just like that it got 1995 if I click in the last cell and hit tab the next product is sunspot from gel boomerangs tab thirteen point 95 and enter now if I come down here sure enough sunspot and that is just plain Excel magic we're using data validation drop-down list vlookup and the excel table feature to make our life easy for looking up prices for a particular product in a dynamic way now there's actually one other amazing use for the excel table feature and that is if this table was actually on a different sheet you would simply convert it to an Excel table and then of course as we did we just type out our name alright let's go look at our next example on sheet 7 now in this example we have a dynamic data source that means we do not even have the lookup table in this file the lookup table is in another excel workbook now before we import it let's go look at that file I'm on the desktop and I have this file source data this is one of the files below the video that you can download we're gonna double click it and open it and there's our lookup table it's an official excel table if I look at table tools the name is product price table now we'll close this file now we're back in our start file I'm gonna select a6 and our goal is to import that excel lookup table into this workbook now in office 365 or Excel 2016 it is amazingly easy we go to get data get in transform group this is also known as power query and get data we can import from virtually any data source Excel workbook text files we could get something from a database but we have our data in an Excel workbook and now we click from workbook we have to navigate to the location and then double click in the Navigator dialog box it will show you all the different objects from your source for us we have a single table product price table and now I click not lo I'm gonna click the drop down and say load to import data we want to import it as a table on the existing sheet a6 is already selected I click OK and that is absolutely beautiful it did two things first we're connected to that external source so when the external data changes we simply refresh and everything updates and it came in as an Excel table so everything's dynamic now let's do two things data over to data validation we want a list source here we learned how to use that for the whole table but you can use the download black arrow to get just the column there's the range in data validation it doesn't use the table name and column name like we would use in formulas but it's still dynamic click OK now we have our drop-down now let's do our formula equals VL tab left arrow comma highlight the whole table there's our table name comma column is 3 because we're trying to get price comment and we're still doing exact match so 0 closed parenthesis now we will get an error because I forgot to choose the product later in the video we'll see how to create a formula to deal with that error but I simply come over and select belen and there's the price now I'm gonna go to the desktop and open that file and add a new record and this is not something necessarily that we would do but whoever's in charge of this workbook they would add a new record there I have added a new record I'm gonna save it close now let's check our drop-down Oh what happened oh this definitely got imported so we have to come over and right-click and because it's connected to a data source that refresh button is there I can click refresh and that is amazing it totally got it from a different Excel workbook and our drop-down is totally working now when we imported this using data get we were actually using power query and when we use power query it opens up the queries and connections there's our query that's going and getting the external data now the thing is if the file that we connected to changes location you need to come here and edit the file path it's easy to do you double click and this opens up the power query editor now all of this was done automatically for us but over here in applied steps step one source you want to click the gear icon and sure enough there it is the file path so if you need to edit this that's where you do it I'm gonna click OK and click close now we want to go to example 8 but before we go to example 8 I want to go remind ourselves what we did in example 3 when we did straight Commission rate we did vlookup to look up 90,000 and returns 7.5 for straight commissions that means 7.5 percent is multiplied by the 90,000 to get your Commission's now both for taxes and commissions sometimes that's the way it is there's a straight Commission rate but often times there's a variable commission rate so for example 8 we want to talk about a variable Commission rate and what we learn here applies exactly the same to many tax situations when you have a variable tax rate now what this means is for a commission rate a contract reads the employee has sales from 0 to 50,000 well on that first 50,000 they get 3.5% anything above 50,000 up to 125 just that incremental amount of 75,000 that earns 4% so for each different tier there's a different Commission rate now locally we have a lookup table in most situations especially for tax which does the heavy lifting our first column has the lower limit upper limit this is the Commission rate so for this category right here that 7.5% gets applied just for amount above 175 up to 225 luckily this row in the lookup table has what we need to make our calculation the total amount of sales from previous categories and this is the real heavy lifting it already calculated all the Commission's from the earlier brackets now what that means is we're actually gonna have to do three different vlookups before we can calculate the total commissions which in this example is total gross pay alright so the first thing we have to look up is the total commissions through previous categories now I can do this in my head right two hundred and fourteen thousand race through find the first one bigger jump back so it looks like all of the Commission's that this person earned from earlier categories is seven thousand six hundred and twenty five alright so we're gonna do vlookup equals VL tab left arrow to get my lookup value comma the table array first column is what we're trying to match I'm gonna put all these in f4 to lock it because we're copying this down comma Commission's through previous categories is in the fifth column so I put a five comma and we do not want approximate match so remember don't put the comma in just leave it out you want to make sure that range lookup isn't bold now we close parentheses control enter that's the correct amount double click and send that down go to the last cell and hit f2 the cell references are working now I went ahead and already did the next two vlookups but the point is that sometimes one vlookup is not going to be enough in our case we had to do three now we can do grosspay okay write e equals this is the commission paid through the previous categories plus and we're only allowed to take the XS since that's the category the XS above 175 all the way up to that amount so in parentheses we have to take the total amount of sales - and there's the amount of sales through the previous categories that amount gives us if I were to highlight this and hit the f9 key that's just the amount that gets taxed times the point zero seven five control-z close parentheses times and now we get our commission rate control enter and I'm going to copy it down go to the last cell and hit f2 all relative cell references now sometimes you want to build it in little pieces like this and do your gross pay our total commissions but sometimes in a payroll table you want to take each one of these vlookups remember that cell is looking to a vlookup that one's looking to a vlookup and so is that one so really sometimes your end goal looks like this a bunch of vlookups now that's beyond the scope of this video but just remember when you get into a variable Tax or Commission rate you're gonna probably have to do multiple vlookups now our next example on sheet 9 is a human resource example and we're gonna get to see for the first time a new lookup function called the match function which will help us do it two way lookup now here's our goal we have an employee name here we want to be able to select whichever employee and we need to extract the correct information for zip state and so on now what this means is when I'm in this cell right here we have to look up Chuck's Hal and get the correct city so that would be Tacoma that means this is column 3 but for zip code we need column 5 each one of these is going to need a different column index number now instead of creating each one manually there's a much better way to do it now think about this if you were looking up zip code and counting on your fingers in this range right here while it's in the fifth position state is in the fourth position well that's where the match function comes in that's exactly what the match function does it looks up an item and finds the relative position of the item in a list so we can before we do vlookup we're gonna use a new lookup function called match and look at that it even has the same name for the first argument lookup value and our lookup value will be the column header names as we copy the formula down so that's our lookup value comma lookup array notice it's not asking for a table you can only put a one-way array here it either has to be horizontal or vertical no tables well that's perfect for us because we have these field names at the top now as I copy this down I need to lock it so I'm gonna hit the f4 key now comma the third argument is almost exactly like vlookups fourth argument now the match function doesn't use the same words as vlookup but less than means approximate match and exact match means exact match now this last option is for when something is sorted biggest to smallest but it's rarely used when doing lookup so when you're using match the first two arguments are the same two arguments and vlookup approximate match and exact match so we're putting a zero because we're doing exact match closed parentheses remember matches a lookup function but when I control enter it totally looked up zip code and counted on its finger and got five for vlookup that's perfect because that's the fifth column in our table when I double click and send this down sure enough it got all of the correct column index numbers now we can use that inside a vlookup instead of manually putting column index number now I want to check the last cell with f2 just to verify that the cell references are correct now let's build vlookup with match in the column index argument f2 now right after the equal sign I'm going to type VL tab now this is polite vlookups screen tip with the arguments or listed if I click inside of Matt wow that's really cool now if we have to put multiple functions in the same formula we can easily get help by clicking inside and seeing which argument we're working on very carefully click after that open parenthesis and there's lookup value now for vlookup we need to look up the employee name so I'm going to click on that cell and we're copying it down and we need it lock so I hit the f4 key comma table array I'm going to highlight the table this is the lookup table for employees and I need to hit the f4 key to lock it comma and there it is column index number we are using the match function to make our life easy so we don't have to manually create each individual formula now come to the end comma and we're definitely doing exact match because we're trying to find exactly that employee name oh there it is approximate match exact match I'm putting a 0 just like in the match function close parenthesis now look at this there it is range lookup I put 0 for approximate match in vlookup there we are inside of match it's also doing exact match for its match type argument now control enter double click and send it down go to the last cell and hit f2 to verify that all the cell references are working that's quite amazing we're using vlookup and match together to do a two-way lookup now it gets even better city I did not want City I actually wanted address and watch what happens when I hit enter it totally works we did not hard code a number here since match is always looking that up it's totally dynamic and enter now we do this vertically you could do the same thing horizontally I'm looking up Chuck's how comma in this table right here f4 to lock it comma and right in column index I'm just gonna type out match now here's where the screen tips really come in handy because right now I'm in vlookup but as soon as I type M a tab the new screen tip pops up lookup value will be the column header name comma lookup array there it is f4 to lock it comma zero because I'm doing exact match and when I close parenthesis the screen tips are polite now I see that match is sitting in column index very carefully at the end I type a comma and then 0 for exact match close parentheses control enter and copy it to the side oh I forgot something well let's go to the last cell and check what we did wrong f2 I'm trying to verify if I got the cell references right and I sure enough did not that was supposed to be locked escaped come back to the first sale f2 click inside of lookup value I'm gonna select it and now I'm going to hit the f4 key control enter copy it to the side click in the last cell and hit f2 now the cell references are working that is some magic match to look up the column index number and vlookup to look up the employee information and enter alright let's go on to our next exciting example on sheet 10 now sheet 10 we're gonna learn how to do fuzzy lookup that's when you have an incomplete lookup value for example right here I want to be able to look up just coca-cola but find coca-cola incorporated Georgia now we can do that we'll start with a straight vlookup equals VL tab left arrow to get my lookup value comma here's the table we're trying to find the company name and return the manager in the second column f4 to lock it comma second column comma zero for exact match close parentheses control enter double click and send it down well of course it's not finding Coca Cola because f2 vlookup is looking for exactly those characters and it cannot find exactly those characters well there's something called a wild card if I put a wild card before and after that cell reference that means to any formula take coca-cola with any number of characters before or after and the wild card is the asterisk now you cannot just type an asterisk you can already see the formula went dark in order to get the wild card asterisks to work you have to put it in double quotes double quotes are the way that we're allowed to put text into a formula and then you have to join that with cell D 11 and you do that by using the join symbol ampersand shift 7 what that means right now is that the lookup value would find Coca Cola with zero or more characters before that means it could find exactly Coca Cola or coca-cola with one two three any number of characters before but we want it on both sides so we say ampersand double quote asterisks and double quote and that's how you do fuzzy look up there's the base anything before or after or even exactly the base now I edited this formula in the middle of a column so I'm gonna have to control enter double click and send it down then I'm gonna drag it up now let's talk about the successes here it definitely found Tyrone Smith cable Wireless was easy to match with this full company name same with chin fam coca-cola was matched with coca-cola incorporated Georgia well these two are not anywhere in the list so we're automatically gonna get an A no matter what the problem with fuzzy lookup comes with do N and do N well those are both in McDonald's and Donna's there is no way that I no off to get around this f2 that's why when you do fuzzy lookup you're never gonna a hundred percent get what you want and the reason we're never gonna be a hundred percent accurate is because right there it really is do in with anything before or after so both of these are a match further when you're doing exact match lookup for any one of the Excel lookup functions when there are duplicates like there are here it can only see the first one so that's why there are some drawbacks nevertheless sometimes people like it because they want to try and get a full match from a partial text item now I want to show you a very important view lookup trick on our next sheet sheet 11 now we have the same exact formula here but the cool trick we're gonna learn here will work on any lookup formula so there's a lookup formula and sometimes it's returning n/a so if we want to substitute something in for the n/a this is the function if and a now this function came in Excel 2013 so any version after that has fna the value that's the lookup function and we come to the end and we type a comma it says value if n/a that just means what would you like to substitute in now I want to put the text no match and because text is going into a formula you have to use double quotes no match and double quotes close parentheses control enter double click and send it down so there we get no match no match now again that if na is great for any lookup formula if na is not going to help us with the inaccuracy of fuzzy look up though all right so if na let's go to our next sheet 12 and we want to see yet another partial text lookup here's a column with transaction ID and the transaction ID is made up of Product ID supplier abbreviation and a date well if we want to look up in return product name we only want the first four characters well luckily there's some great text functions in Excel left right and mid you can get text from the left from the right or in the middle so for us the text we want is always four characters from the left equals left the text that's a relative cell reference comma number of characters we always want for close parentheses control enter double click and send it down and there we have our lookup value but there's one problem that number and this number right there look different and the reason why is in Excel text is always aligned to the left numbers are always aligned to the right and any one of the left-right or mid functions or any text function always delivers text now if we put this into vlookup it just wouldn't work now we could put it into vlookup and get the error but let me just prove it to you is 1498 and i'm going to use an equal sign as a comparative operator hey not 1498 are you equal to this 1498 when I hit enter of course it says false because in excel text numbers are not equivalent to actual numbers well luckily there's a great trick to convert text numbers back to numbers I'm going to hit f2 you can do any math operation on this right here as long as it doesn't change the value if you multiply by one control-enter well there it converted it to an actual number any math operation you could divide by one f2 you could add zero you could even do a double negative in the front I'm gonna leave it as adds zero it's a math operation it doesn't change the value so when I control enter now I have a num that I can match against that number and we can sure enough see our logical formula over here says we're good to go now I'm going to double click and send this down come to the top cell f2 and now we're gonna use VL tab the lookup value is exactly what we want comma table array come over here and we're copying this so we need to hit the f4 key comma 2nd column has product name 2 comma 0 for exact match closed parenthesis and there is extracting partial text and converting it to a proper number in lookup value inside a vlookup control enter that is so cool double click and send it down go to the last cell and hit f2 we've ever find that the cell references aren't correct I'm going to hit enter now let's go over to example 13 now in this example we have a lookup table and this is the column we're trying to retrieve items from but sometimes the column in the lookup table will have empty cells so I want to see what happens when we do a vlookup to this item what happens when vlookup returns an empty cell equals VL tab left arrow to get lookup value comma company is the first column we're trying to retrieve manager in the second column comma 2 comma 0 close parentheses now when I hit enter of course Bath College will work because Sue rad cool inator is there but when I select cable wireless wow vlookup returns a 0 well in fact any formula that points to an empty cell returns a 0 now if our goal is to have the formulas show nothing rather than the 0 anytime a manager name is missing then we're going to use a simple trick we're gonna join it using the ampersand shift 7 and we're going to use a strange text item we're going to use double quote double quote this is the third time we've used double quotes anytime you use double quotes like this you're putting text into the formula now because there's nothing between the two double quotes this is called a zero length text ring and in Excel that's how we can get our formula to show nothing by the way if you wanted some alternative text then you'd have to use a different formula with the if function but now when I hit enter that achieved our goal if I select Donna's sure enough chantel but pcc's better show nothing so vlookup and join a zero length text ring now we want to go to our fourteenth example and this might be the coolest one so far now here's the situation we actually do need to look up the price of a pipe were selling pipes for this transaction we sold three hundred and twenty four that's a two inch pipe and we need to look up the price down here we have to look up the price of a ten inch pipe the problem is the lookup formula right here needs to find the uPVC table which is right there when I get down to this transaction it's a different lookup table I need my formula to go to P PVC this second table so the lookup problem we have to solve here is how do we get our formula as we copy it down to look to the correct first second or third table well there's an easy way to do it because we have a type column for each row we have an indicator that is uPVC that's text it tells us to go to this table when we get down to this record right here P PVC will tell us to go to this table but that means we need to name each one of these tables naming a range is easy in Excel I'm going to highlight and remember this is a lookup table so we don't include the column headers go up to the formulas ribbon tab and there it is defined names group we click name manager now remember my icons might different than yours but clique name manager and here's our dialogue box we can click new and most importantly we can come back and edit and delete later I'm going to click new I want to call this range right here the exact same thing that's listed in the type column uPVC now notice down here it shows the range if you ever needed to edit you come back here and edit I'm gonna click OK now I showed you the name manager because it's important to know how to edit and delete names using the name manager but when we're creating new names there's an easier way I'm gonna click close will highlight the second table and right up in the formula bar all the way to the left hover your cursor and sure enough that's the name box so I click and now I'm going to type P PVC and enter uh-oh I definitely want it capital even though Excel would consider that equivalent to that right there so I actually did make a mistake I'll have to go edit it but now I'm going to use the name box highlight the third lookup table come up here CPVC and enter now unfortunately I made a mistake so I'm gonna go to formulas name manager click on that and edit and I'm gonna change it to capital again that wouldn't have mattered because Excel considers the letter C whether capital or lowercase equivalent click ok now we have our names click close and here's our simple formula equals VL tab I'm gonna left arrow to get the inches that's what we're looking up in the first column of whichever table it is comma and I'm gonna use my arrow key to arrow over that represents these tables now actually that's text these are defined names over here but because it's in table array it should work comma we are always looking up from the second column so for column index I put two and guess what we're definitely using approximate match look up every single time we're searching through finding the first bigger one and jumping back so we completely leave out range lookup because the default is approximate match close parentheses control enter and a yes there's a problem f2 right here if I highlight that and hit the f9 key which is the evaluation key Excel properly thinks that it's text because it is text it's not the defined name ctrl Z you are not going to believe this there's a function built exactly to solve our problem it's called indirect and all it does is it takes text that represents a reference or a defined name or a range of cells and converts it to that reference so when I wrap indirect around that text item now in table array if I hit the f9 key that is amazing the indirect took that text item which represented this table right here and converted it to that defined name the correct lookup table and you could see 115 95 sure enough to 2295 it got the table right control-z control-enter Oh F to look at that I accidentally have a minus there so I backspace ctrl enter or double click and send it down and that is amazing now let's manually check to sure enough in the first table down here that's 10-4 PPV see sure enough 95 95 now let's verify again F to click inside vlookup click on table array f9 we're verifying and sure enough one 1295 there it is the last one 10 95 95 it got the correct table ctrl Z now that is such a cool trick indirect function and define names to look up to multiple tables now we could complete this by multiplying number of pipes times price and getting our total sales but we'll leave that for another day look up to multiple tables alright let's look at our last example on sheet 15 to look up values Wow look at this if I'm gonna look this up right here there's a code but that code represents both left and right handed so right here we actually have two entries into the first column of our lookup table so when I'm right here I need to look up that code with an L on the end but when I'm right here I need to look up that code with an R on the end no problem we can do that equals VL tab lookup value well guess what I need that but I also need to join it to the L and how do we do that the join symbol ampersand shift seven now when I click on this one I'm gonna have to lock it with the f4 key and there it is right and lookup value I could hit the f9 key and verify it is now a single item control-z now I could finish the formula comma there's the table lock it with the f4 comma 2 comma 0 close parentheses control enter double click and send it down go to the last cell and hit f2 there we have our correct cell references now watch this I'm gonna cheat f2 copy this in edit mode ctrl C escape f2 ctrl V and here's a great trick if you point to the edge of the cell reference that's the selection cursor but that is called the move cursor I'm gonna click and drag that is so cool control-enter double click and send it down go to the last cell and hit f2 now there is one other way to do this I'm going to delete everything but the top cell here and it involves something different than relative and absolute we're gonna talk about a mixed cell reference now what do I want d5 to do well I want it to move as I copy down but when I copy the vlookup to the side oops it needs to be locked on the D column so the way you do that is you hit the f4 key one time then two times that puts the dollar sign just in front of the row reference a third time locks just the column that means as I copy this down the five will move to six seven and so on but when it copied to the side that D is locked we can do the same thing here I'm going to double click and now I'm going to hit the f4 key once just a single time now the four is locked so as I copy the formula down the four doesn't move to five six seven and so on it's locked on that L but look at that I did not put a dollar sign in front of the e so when I copy the formula from the e column over to the F column the e is allowed to move to F because there's no dollar sign now the cool thing about this is this formula will work throughout the entire table control-enter copy it to the side double click and send it down go to the diagonally furthest cell away and hit f2 that is some magic it's looking at the correct all code and the table wow what an amazing way to end this video mixed cell references to lookup values wow that was an amazing 15 examples and over here on the topic sheet I put a picture of each one of the formulas as a quick reference guide for you so you don't forget use 0 for false leave it out for approximate match all the way down to don't forget you can join two values for your look value hopefully the mysteries of the vlookup function have been revealed hey if you like this video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from excel is fun and don't forget when you click that subscribe click that Bell icon alright we'll see you next video [Music]

Channel: ExcelIsFun

Views: 61,887

Rating: 4.948144 out of 5

Keywords: Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, EMT, EMT 1515, Excel Magic Trick 1515, VLOOKUP Function, How to use VLOOKUP, My VLOOKUP Does Not Work, VLOOKUP Error, VLOOKUP #N/A, Learn VLOOKUP, Basics VLOOKUP, Advanced VLKOOKUP, What does VLOOKUP do?, MATCH Function, VLOOKUP & MATCH Functions, IFNA & VLOOKUP Functions, INDIRECT & VLOOKUP Function, VLOOKUP Explained, LOOKUP Help

Id: LdhLAa-Vqmg

Length: 69min 26sec (4166 seconds)

Published: Fri Sep 14 2018