Learn VLOOKUP in Simple Steps

hi I'm Jamie and welcome to teachers Tech today I want to show you how to use vlookup in Microsoft Excel for beginners I have a few examples that you can follow along with me with the practice file that's right behind me just go ahead and download it then we can get

started vlookup is a very powerful function that you can use to look up something in a column vertically that's where the V is going to come from so it looks down a column that you set because it's going to be the furthest left and then it returns uh some item in a that corresponding row so in this case here I already have V look up in this cell

already created I'll create a new one here if I just go ahead and select this cell you can see in the formula bar it's already there but what it's going to go through so it looks at GOC right here and it finds it I told it to go down the furthest left of the selection and this is this uh vertical column here it finds GOC then I told it to look up the uh in the in that Row the salary here so it gave me back 68,000 so let me just change this so instead of C I'm just going to go put I here and it returned 58,000 so it went over to the furthest left here of the range that I selected and it went down and it found goti right here and it went across and found $58,000 so this is a small data set here uh and you if this was a large one you would probably see how much more powerful this is but if you had thousand of employees and you're looking up something like this with this formula you can simply just do those uh searches really quickly now uh something I just wanted to point out here with v lookup I was just taking a look at the X lookup function because X lookup has a few more capabilities to it uh with vlookup you can only look to the right uh but X lookup you can actually look left or right and with X lookup you can kind of do the H lookup or V lookup together so it's something I would recommend if you have a new newer version of Microsoft Excel I do have a video tutorial on that and I'll put a link in the description down below and up and above in the card so let's go ahead and start our V look up and build

it for this sheet and if you haven't got this sheet open hit pause download it and follow along so we're going to go through the vlookup function a couple times couple different examples and two different cells the first place we're going to put the V look up function is right in this cell so what I want to have happen is it's going to look up an employee name and bring me back an employee designation so it needs to go over to this column here and then look down this column here to find uh that the designation in the same uh row that it found the employee name so we're going to start in this one the first time we're going to go and just go insert function up here so open up that one insert function if you don't see V lookup just do a quick search you don't need to type the whole thing here I'm just going to hit go and then double click on it so we get our four different arguments here so the first argument is going to be the lookup value well the lookup value is what the name is that I type into this cell so this is going to be the lookup cell or lookup value in this cell right here so I'm going to click in I6 and you can see it put I6 into there the next is the table array so I'm going to click in this spot and the table array is going to be the selection and remember it's going to be the furthest left column of that array that is going to search down so it's going to look for a name here uh and so I need to make sure this is the furthest left column and it is so I'm going to select here and I need it to go to at least go to employee designation um it could go all the way to salary if I wanted but it doesn't need to uh but I'm going to leave it right there for this one now the next one is the column index and this is how it works so in B this is the first part of the table or range here so this is column one even though I have a here there's nothing in it uh this doesn't count so B is number one C is number two D is number three e is number four and F is number five so we told it to it's going to look down the furthest left of my array that is this one and I need it to look down what's the column I needed to search through well it's going to be D right here and that's three because it's 1 2 3 so I need to put three in here just like that uh so the next thing what I want to do is uh type in a true or false I could leave it blank too but in this case I'm going to type false and that means an exact match if I put true it's an approximate match but I wanted to find an exact match in this case so I'm going to type in false like so and I'm going to hit okay and it came back notice it came back with na here uh that's because I don't have anything typed in so let's just type in John here and we'll type John and it returns software engineer so went to the furthest left of the array that I showed that I selected and then it went to column 3 and found what was in there so that's software engineer so if I go ahead and type brand here it went down this column the furthest left and then it search column 3 and brought back analyst uh so this is working quite well you can see it doesn't take a lot to create the vlookup function one problem is if you're using uh faults I'll show you the problem if we go to Brand here and I'm going to just click in here and let's say there's some spaces just like that notice it went to na it's because we are looking for an exact match uh and you got to be careful not if you're using exact match not to have spaces so as soon as I get back I delete that again I and hit enter you notice it works again so I just wanted to point this out okay let's run through V lookup one more time so I'm going to go ahead

and recreate the top one from up here for vlookup now the reason I'm going to do this because it's a little different on how to count the column so I'm going to go uh what do I want to have happen well the V lookup is going to go into this cell so it's I 10 and I'm going to start my function this way this time just by putting the equal sign then V look up and I'm going to choose it right through here so what is my lookup value going to be well the lookup value is going to be whatever I put into this so this is going to be the code and I'm just going to go ahead click on that cell I9 and comma what's going to be my table array on this one well my table array this on this one is going to go from the code up here to the salary one here so there's going to be four different columns uh in there and then at the next point I'm going to put my comma what's my column index and now this is where things have changed so remember last time I said B was one now that the array is starting here this is going to be one so C is 1 D is 2 e is three f is four uh so it's wherever the array is going to be starting on it so I just wanted to point that out so what do I need uh this is going to be 1 2 3 4 so I have to put four for my number on it because this is not selected in it so I'm going to go ahead put my comma and then put my exact match again and that should be all I need I'm just going to finish off with the bracket and let's try this out so if I just put in goti like so and you can see that goti here was $58,000 here so I just wanted to point out when you were when I was doing the V lookup function this way is making sure that you understood when you count your columns it's wherever the array starts uh that's going to be number one and then you move over from there uh and then if you have to go back and correct anything uh you can just go back uh you can either correct it up top here or double click into the cell and it will open up so if you wanted to change this to true or false you can move those back and forth or change a different range too if you want to keep learning about Microsoft Excel and V lookup take a look at this video I created about how I created a multiple dependent drop-down list in Microsoft Excel and finished it up with v lookup thanks for watching this time on teachers Tech check out that video I'll see you next time with more Tech tips and tutorials