welcome to the ultimate Excel lookup tutorial where we'll go over the vlookup the hlookup and all the features on the X lookup from the very basic a level one all the way to the hardest a level five let's go first up we've got the vlookup which basically looks for a figure within a vertical table so over here in this Excel file which you can download in the description down below you can see that we've got the salesperson and we want to find out what their commission was so this is the sale amount that they had
and this over here are their commissions so in this case for for Harley here we want to find out that person's commission so we're just gonna go equals vlookup and here it's going to give you the description of it just press the top key when you start to see it for the lookup value so we're looking for a Harley in this case press the comma key the table array is going to be the whole range that we're interested in in our case it's going to be this whole table so just hit the top one then
just press Ctrl shift down arrow and Ctrl shift right arrow press the comma key there and now it's going to ask for the column index number basically how a vlookup works is that it goes from the left hand side all the way to the right so as of now the sales person is the First Column and because we're interested in the commission that's going to be not one two three four but the fifth one over here as a commission so we're gonna hit a five there comma and we want an exact match so we're just
gonna go with files press the top key there close the brackets and hit enter so now you can see that Harley has a commission of 1200 if you go over here that's exactly that commission and what's nice about this formula is that it's Dynamic suppose I change from highly Fritz to something like Yvonne Heinz which is somebody else in this data set and you're gonna see that that person's is right here and that number automatically updated as well while the vlookup makes sense for vertical data as you can probably imagine the hlookup makes sense for
horizontal data so let's look into that just go to control page down that's going to take you to the second Tab and over here we really have the same data set other than it's laid out horizontally now for there we just want to find the exact same thing so the commission for Harley here so we're just gonna go equals hlookup press the top key and the syntax here is going to be pretty much the same so the lookup value is hardly we'll press the comma the table array is going to be this whole table over
here so we're just gonna go Ctrl shift down Ctrl shift right come again the row index number remember this time it's on the column index but the row index and we still want it to be the fifth one so we're just gonna press the 5 comma and we want an exact match again close the brackets and hit enter that's gonna give us the same number as I did with the vlookup as you can probably imagine the vlookup is a lot more popular primarily because data is usually sort of vertically instead of horizontally like this which
honestly is just a bit hard to read while these two functions can come very handy they do have some limitations if you go control page down to the third tab you'll find over here that the data set has been slightly arranged where the salesperson is actually all the way on the last column instead of on the first like it was before now in this scenario if we try to do a vlookup we'll go equals vlookup press the top key there local value is Harley comma the table array is gonna be this whole area over here
so Ctrl shift down Ctrl shift right the column index number and this is where we start to have an issue is that typically the vlookup goes from left to right but in this case we need to go the other way around because the salesperson is our last column so suppose to get the commission we just type in -1 say and then I'm gonna type a false for an exact match close the brackets and hit enter here but unfortunately that's not gonna work because I can't actually do the minus thing instead it always has to go
from left to right but luckily for us we do have the X lookup to come save us and you can see it as the vlookup on steroids so let's go over five different scenarios where it might come handy from easiest all the way to hardest so going to the Excel file just where we left off under the vlookup here which didn't quite work let's test it out with an X lookup instead so we'll go equals x lookup press the top key the lookup value is the same hardly comma lookup array this time we're only going
to select this whole column over here so just press Ctrl shift down here comma and the returnary will specify that we just want that the commission column so Ctrl shift down here close those brackets and hit enter and just like that using the X lookup we don't have the limitation of only going from left to right anymore now moving on to level 2 of The X lookup and over here we have the exact same data set but instead let's see that as a salesperson I just type something like kanji and you'll notice that the whole
X cell formula here breaks down and the reason for that is that Kenji is actually not on this list and so it's not really finding that much now instead of getting this ugly N A Sign you can modify it to see something that actually makes sense somebody that's never seen the formula before so you just press the F2 key to get inside the formula go towards the very last parenthesis there and it's going to see the if not found feature and here's where we want to type something like not on the list close the quotations
there and hit enter and so for someone like Kenji it's gonna say it's not on the list however if I change this to say Ivan Heinz hit enter there then that commission is gonna automatically update nice now looking into level 3 of The X lookup just go to control page down over here and in this scenario you'll find that it's a bit different in that both the sales amount and the commission are the things that we're looking for so not just one or the other but actually both so for this x lookup actually has a
feature where if you just type X lookup over here same lookup value we want same look up array which is going to be the sales person over here but now the big difference is that for the return array we're not just gonna select the sales amount but we're actually going to select both the sales amount and the commission so Ctrl shift down Ctrl shift right and we'll select both of these columns close those brackets and hit enter there now you'll see that both the sales amount and the commission have updated and if you look at
Harley's that's the exact same amount here and if we way to change one of these say Yvonne Heinz then all of a sudden both things are actually going to change for us and they look just right as well all right now moving on to the harder stuff a level four and here we're gonna be working with a slightly different data set so go to control page down the main difference here is that instead of having the sales person we're gonna have the company name so suppose that for instance we want to find the sales amount
for Nike but to be honest it's not exactly called Nike instead it's called Nike Inc and we might just remember it as Nike same thing with apple maybe we thought it was just called Apple but instead it's called Apple emia so let's see if x lookup is able to make that connection for us so here we're gonna go equals x lookup press the top key the lookup value isn't just gonna be Nike because it's not going to find it here because it's called Nike Inc so instead we want to specify that it's Nike and some
other information that we don't quite know so we're just gonna put this ampere sign put quotations and we're gonna put the asterisk which basically means that hey it's for an undetermined amount of range after Nike as well then close the quotations there comma the Loca Pari is going to be all the company names so Ctrl shift down comma the return array we said we want to find out the sales amount so Ctrl shift down there comma if not found we don't want to put anything there so we just press the comma again and now here
for the match mode we don't want an exact match because that's not really gonna match anything for us so we're just gonna put the two there wild card character much is the one that's gonna work for us best here close the brackets there and hit enter now you'll notice that for Nike it's giving us a sales amount of 6000 which seems just about right so now suppose I change the name over here to something like Zara then it's gonna be able to determine that it's actually not called jazara but instead it's called Zara fashion and
that's the one we should be looking at same thing goes if I type say h m that's going to be able to associate it as well so this applies to not just company names obviously but if you only know the first name of a person or the first few digits of their code say and finally we're heading into level five and congrats if you made it this far so let's go to control page down over here and we have have the same old data set but in this case the main difference is that we have
two criteria to filter by one being that it has to be this sales person and secondly that it has to be in this particular year now to do so we're gonna be able to use the X lookup but it's going to be a bit more mixed so we're just gonna go equals x lookup press the top key firstly we want to find out that it's this sales person so we'll hit the comma there and the lookup array is gonna be the whole range over here like we've been doing all the way till now but however
for the return array this is where it gets a bit more tricky because we also need to account for the years that they have to be same to this year over here now to do so we're actually going to put an X another X lookup inside of that so we put X lookup press the top key the lookup value in this case you can't quite see it but I'm selecting the year over here comma the lookup array is gonna be all of the years we have available over here up top comma and the returnery for
us we're looking for the commission so that's going to be all of the data set over here so just all of the figures inside of that close the brackets close the brackets again and hit enter that should give you a highly freezes Commission in the year of 2020 which is gonna be this one right over here where I had to change this say to 2021 that should move along accordingly to this figure right over here as is the case so as we've seen the X lookup is a beast but it does have some limitations let's
go to control page down over here and you'll notice that this time around Harley has made more than one sale and so we want to account for that person's commission with multiple sales now to do so we have the exact same formula that we used earlier but the problem is that it's only accounting for one sale it's only really accounting for this one in particular instead of accounting for all four of them in 2019 and that's a problem with xlookup or the lookup functions in general and instead if they have a much then they're only
going to pick the very first one and ignore all of the other ones below so suppose we want to find out how much this person earned in Commission in 2019 not just for one sale but for all of their sales now to do so we're gonna have to get a bit more creative and use a different formula here by ditching the lookup functions so let's give this a try we're gonna have to use what's known as the filter option first so we're just gonna type equals filter press the top key and so we want the
array to be all of the figures for us so all of the data that we're interested in so all of these here comma and we want them to include firstly the the name so this range over here all of the names that we've got we want them to equal to higher lease now close those brackets and hit enter what's that's going to give us is basically all of this data over here the reason it gives us everything is because we haven't quite filtered by the year as well yet right so instead we're gonna have to
get back inside of it at the very beginning we're gonna put another filter this time around just for the year so we'll go equals filter press the Tab Key and for the array we're fine with this being the array comma and under include is where we want to do the year feature so we're going to select all of the years here make them equal to the 2019 year close those brackets and hit enter and now you'll see that it's filtered down only to the 2019 figures and it's selecting all four of them so all of
these four over here for Harley which makes sense if I change this to 2020 say then it's going to change to these four over here but we're still not quite finding the sum here instead we just have these four numbers which is kind of hard to justify to our boss say if we send something like this so instead at the very beginning we're just going to put a sum press the top key which is going to sum all of those figures for us close the brackets and hit enter and that's going to make it look
nice and clean for us so this right here we're looking at is the sum of these four this is going to change dynamically say I put 2022 that's going to change to these four over here for more on Excel check out this link over here to learn the biggest mistakes to avoid using Excel or this other link over here to pick or Excel for business and finance course hit that like and the Subscribe button if you liked it and I'll catch you in the next one
