How to use VLOOKUP in Google Sheets

have you ever found yourself scrolling through  endless rows of data trying to find that one   matching piece of information what happened  if I told you there's a simple formula that   you can use that will do all the heavy lifting  in seconds today in this video I want to show   you how to use vlookup in Google Sheets this is  going to save you a lot of time hi I'm   Jamie and welcome to teachers Tech if you want  to follow along with today's tutorial I'll put   a link down below to this spreadsheet so when  you click on it it's going to create your very   own copy we're going to start with the basics of  vlookup in Google Sheets but then we'll progress   with a few different examples to make sure  you get a good handle on it all these will be   quick to do so you can start using this right  away to save you time let's get started let's

start with some basic examples of the lookup  here I wanted to look up an ice cream that I   tell it to and give me back the corresponding  price so it will go down this side of the range   this list here and find something like chocolate  and return back that I know the price is going to   be 203 so if I go over here to this spot this cell  this is where I want to put my formula and I'm   going to start my formula with the equal sign and  then I can start typing my V look up so if I start   typ V then L you're going to find it come up right  away let me walk you through the different steps   of this V lookup now the first thing is going  to be this search key and I want it to look up   chocolate I could either sell reference it and I'm  going to show you an example of each or actually   just type in chocolate but let's do it this way  at first so I'm going to go ahead I have to give   my quotation marks and now I'm going to go ahead  and type chocolate and spelling matters or else   it won't find what it's looking for so I can type  my chocolate like this and my quotation marks so   that's the search key in this example I place my  comma what's my range going to be well the range   is going to be the top part of the data set which  is vanilla and the bottom here to this 210 so if I   highlight these uh like this notice it goes B4 to  D13 that's my range that I have for this example   then I can place another comma the index is asking  me in this case what do I want it to return what   column so I want to return price so these  columns are going to be numbered from this ice   cream flavor is number one Scoops remaining  is number two and price is number three then if   I had another one after price that would be number  four but so this is number three I'm going to put   the number three in right here and then I'm going  to go ahead give it another comma notice it says   is sorted so by default if I leave it blank and  don't put anything in it's going to be true but   I recommend that you choose false on this and I'll  show a different example where you might want to   use uh each of these as well but I'm going to go  ahead and type false for these first examples and   I'm going to go ahead and hit enter and return so  what did it do it looked at this list on this side   here and it found the chocolate and it returned  the index the column I told it to return was price   at 203 let's do one more quick example and this  time we're going to sell reference it and this is   what I would prefer to do so if I go ahead and  look just start my formula same way V lookup I   start typing it in what's my search key this time  I just have to click on the cell so it's going to   be G6 whatever is going to be in that cell is what  it's going to be looking up the search index I put   my comma the range is going to be the same from B4  to d113 place my comma my index I still wanted to   bring back price so it's going to be number three  and put my comma and I need to make sure that I   say false well recommended that you put false in  here for an exact match and then I can hit enter   and it brings me back 210 here's butter be pan 210  so now if I was going to just type something else   in like this notice so strawberry it went and  found strawberry $1. 75 because since this was a   cell reference now I can quickly just change this  and like I said this is the way I like to work is   with the cell reference but I wanted to make sure  you knew that you can do both I'll leave this spot

right here for you to try on your own let me know  if you get the correct answer Down Below in the   comments let's move on to uh this example here  and this is a quick one it's just going to be   about changing that index column and I'm going  to go ahead and start my equal sign andp put V   lookup because rather than the price this  time I wanted to return how many scoops on the   ice cream that I choose that are remaining so in  my search key I'm going to sell reference so I'm   just going to click on G3 to sell reference it  place my comma in my range is going to be from   B4 to D13 Place Another comma and here's my index  so last time I was going to the third column I'm   going to put to the second column because I want  the Scoops coming back this time so I place two   I'll place my other comma in here and I'm going  to do false again for the exact match in my next   example like I'll talk about the false versus the  true on this point but I'll put my enter and then   we have cookies and cream 7 if I look for cookies  and cream 7 I can go at this time change it to   what ever I would like it to be and it should  change accordingly so vanilla to1 so making   sure that you understand what that index does if I  change that number in this third example I want to

talk about approximate match and exact match now  this is the true and false part that I've already   mentioned in a bit let's go ahead and start with  an approximate match and that's leaving it to true   and see what happens here so I want this time to  look up an ID and return that ice cream flavor   so 128 should be strawberry if I go ahead and  just start putting my vook up in again just like   I've done already and the search key will sell  reference it comma the range is going to be from   here A4 to D13 another comma I wanted to bring  back what column is this well this is number two   so that's the index here and if I leave it blank  if I don't put anything it will default to true   or you can go ahead and write true like so now the  first thing that I want to mention is that you can   see that this isn't going to be correct 128 isn't  uh butter pean but what you need to do if you are   using the true which is the exact match you  need to make sure that these are uh placed in   order so sort so if I go ahead and just highlight  this area and go up to my data here and I'm going   to sort the range A to Z Now notice it's all in  order if I look at 128 now 128 is strawberry if   I was going to go ahead and change this let's say  to 117 here it should say chocolate and it does so   it's important to make sure that is uh part  of it now the other thing I want to mention is   when you're using this approximate match let's say  if I put a number uh like 104 and notice there is   no 104 here so if I put 104 here and hit return it  returns mint chocolate chipped so it's looking at   an approximate match so 104 I put in it had 105  or 103 but it picked mint chocolate chip so if I   was going to a number again like 111 it's still  going to bring me back something and you might   not want that to have happen so this is where  the exact match if I was going to go over here   and put my vook up and I can go ahead and pick  my search key my range my index and this time   I'm just going to put it to false like so so  now if I was going to go ahead and put a certain   number so like I said that doesn't even exist so  if it was going to be 107 let's put in 107 notice   we get the na they couldn't find that value so  that's better than probably not having an exist   but let's say we don't wanted to have an A like  this and we can put our own message and I'm going   to show you how to do that now if you're getting  that error message the na1 and you want to replace

it with a custom one go ahead and do this we need  to add another function now and so I'm going to   go and put my equal sign in and then type if na  so this is when the value is not na or otherwise   second argument so we're going to tell it what we  want it to say so I'm going to choose this one but   I need the V lookup also so I need to put the  V look up nested inside of here and I'm going to   go ahead and do my search key so this is going  to be cotton candy here are this cell reference   the range just like I showed you before the index  what do I want it to have come back it's going to   be price so that's going to be number three column  and I'm going to put a comma keep   this to false here I'm going to close this put  the comma in and what do I want it to say so I'm   just going to give it a space and I'll say inside  quotation marks here not and I'll capitalize this   not found just like this and I need to close this  so this bracket and this bracket close and these   two close so you always have to make sure that you  have the open and the closed one so I'm just going   to hit enter notice cotton candy isn't on this  list any and I could have put any message that   I wanted to and I could go back to it by double  clicking and change this to something else if I   wanted to if I was going to choose uh let's go  ahead and write vanilla in here we'll find the1   50 and if I was going to say let's say pencil for  some reason not found so rather than having that   na use the ifna a function with the vlookup  to get your own custom message now I have a

hack for you that I want to show you and it's  how you can use multiple criteria with vlookup   so in this case I'm going to put my vlookup right  here I wanted to be able to look up two different   things so it's going to look up Jim and it's going  to look up Helper and then I want it to bring back   the department and notice I even got a couple gems  to show you how it can break that down so it can   give me back the correct department but the first  thing I got to do is add a helper column and I'm   just going to go ahead and add it right here I'm  just going to call this Helper and I'm going to go   and combine these two columns on each of these  names so if I go and start my equal sign where   I'm going to put my formula I can go and click  on the cell C4 use the Amber s sign or the and   sign above the number seven and then I can click  on this one right here so it gives me it shows   me what it's going to look like it just puts it  all in one so now I can just copy this down and   I have all these new uh ones that I'm going to be  using in my helper column so now I'm ready to go   ahead and do my vlookup with multiple criteria  I'll put my equals in start typing my vlookup   what I want as my search key well the first one's  going to be gy I'm going to use and what's the   other one going to be it's going to be helper  so I can put those two together like that I'm   going to use a comma what's my range going to be  well it's going to be from this one to the bottom   so B4 e14 comma what do I want to have return so  this is going to be the fourth column here where   the department is so I put my number four and I'm  going to put this to fals here and I'm going to   go ahead and close it up so it brings me back  Jim helpert is in sales so you can see here it   is it looked at those two different things so  if I was going to go and change this last name   to Lee look how it went through this time it was  matching the gim Lee to the warehouse so that's   a cool feature if you make that helper column  you can use multiple criteria with v lookup I   hope this has given you a good understanding of  how to use vlookup in Google Sheets let me know   what else you're looking to learn if you want  to learn more about Google Sheets I have other   videos as well and I'll put the link Down Below  in the description and up above in the card thanks   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials