 Hello everyone and welcome back to another tutorial in this video We're going to be introducing you to the new x lookup function available to us in Microsoft XL predominantly office 365 365 version of Excel and obviously what the real benefits are of using this function and obviously at the end of the video You can come to the conclusion Whether you think you'll start actually using x lookup more than vlookup because it does have some really key Benefits or I'm sorry say improvements over the vlookup formula. So as all of our videos, we've got a sort of a working Demonstration that will go through sort of like a real-world example of how you can use this formula and Yeah, there's probably not much else to say rather than just jumping in So like we said the x lookup works in the same way as the vlookup apart from it's in some way simpler And it has a lot more flexibility when it comes to using the function So the scenario I'm going to use here is you can see we've got a list of names Down the side here. So we've got Daniel Cody so on and so forth. So what seven names? We can see what a yearly salary is and we're going to be using the x lookup formula to or function to populate the Department current department and the bonus percentage So this information is stored in our second tab over here called the data sheet And you can see we've got a full list of employee data And we've also got some bonus information there as well We're pointing out as well. So the three main fields. I've got here's obviously the individuals name the department in which they're working And so we've got like various departments from a business. So you can see we've got everywhere from human resources through to sales and Each individual and their department is in date order from oldest to newest And then in addition to that you can see under the bonus scheme. We've got some bands of salary. So We've got 10 25 50 75,000 and then we've got a hundred and a hundred and twenty five thousand there as well And depending on the person's salary, this is what determines what bonus they will be getting So obviously if they've got well as it stems in this example the higher the salary the higher the bonus percentage They're going to get and it's worth noticing or not noticing but noting because all this data Obviously has got some names and you'll see some sorrow information here, of course, this is all randomly generated information. I've just done Just basically by rendering the entering information into the Excel spreadsheet Hopefully that's a given and be obvious, but I just want to make that statement there So yeah, all of this is completely random data. So should you see your name in that column a it is a completely Coincidental very small likely in the case, but yeah point point may so this is the data sheet We're gonna be sourcing our data from so what we'll do that's a further delay. Let's just jump straight in and look at how to use the function So the first thing we want to do is populate the department So in order to do so all we need to do is as we would be look up start typing But we're gonna this time for X look up and open brackets So the first thing we need to do is so look up value. So that's gonna be our name So we need to do select a five in my example here comma We're then gonna go to our data tab As you who are very familiar with Vluck will know we would now then go and select the full Array or range of our data making sure that the look up and value and the return value was all in that same range We don't need to worry about that now All we want to do is having put our lookup value The next thing to do is enter in our lookup array So this is where we want to look for our lookup value What we'll do is let all these names in here Shift and down and I'm just gonna do F4 as well just to make sure that that range is locked because we'll be dragging this formula down So that's our lookup array populated And the second part is or the third part to say is the return array. So what piece of information do we want to return? Well for us that is just going to be the department. So again, I'm just gonna select all of the department Like so hit F4 again just to make sure it's locked Close brackets because that's all we need now We've got three other pieces of information we could add there, but they're all optional We don't actually need to add them so close brackets and that is it That is the entirety of the formula we need for X lookup So we've got three parts. The first part is what value do you want to look for? The second part is the range you want to look for that value and in the third part is the range Containing the value wish to return and I've just noticed there's a little error here It's got the sheet name twice so it's going to remove that out there So do excuse that And then when we hit enter you can see we've got a value returned So we can now copy this down and this is why I locked these ranges to make sure that they're going to be fixed And let's copy this information down and we can see there we go We've got the department and put all those individuals So that all works really well One thing to know, uh, you wouldn't notice it's probably off the bat But I know this from looking at the data. We know that Daniel Brooke His actual his current department is no longer sales If I was going into a data sheet and I go and search for Daniel Or brook Daniel. So I've got the wrong way around We can see that his original team was or department was sales But he actually moved into technology in september of 2019. So A little less than a year after he originally started in the sales team So obviously this is not the information we ideally want. We don't want just the department We want that so i'm mixing the two together So first in column c we can see we've got the department that first gets pulled through to us But actually we want what their current department is So we know it's not sales. His current department is actually, uh, I believe it was technology, wasn't it? It was And yeah, so it is technology here So what we want to see is actually we don't want the sales. We want technology So this is what we're going to put it through here And this will allow us to see if there is any other circumstances in our data where someone is Or has changed their department in which they're working So previously when we were looking we would be using vlookup Now i'm doing a lot of comparisons between the two, but hopefully it's beneficial to for you to see what the true benefit is With vlookup, you know, we would just be very much stuck with okay Vlookup will just return that first value that it comes across in a list The benefit with xlookup is we're able to actually, uh Enforce what way the information is going to pull from so rather than going straight from The first value that comes across starting at the top We can actually have it look for the first value that comes across but starting from the bottom of a range And all we need to do for that is just until exactly the same formula we've got here And just to save time. I will literally just copy this out, but you can type it back in there again if you so desire Go into And i'm just going to close remove that last bracket at the moment And we can see at the moment we've got obviously our three Options we can use at the end here. So the first one we have is if not found. So this gives us the ability to Define what happens if the function is unable to find your value And the second part here is obviously the match mode. So how do we want to match? Do we want to do an exact match or do we want to do one of these other options? And we'll be coming on to this section When we look at populating the bonus I won't touch it on too much now So what we're going to do is do another comment to get across to this search mode So this allows us to have obviously the flexibility I mentioned So by default we'll have the search first to last and that's obviously the one we see highlighted here with the number one And that's the option that vlookup will always be using But we want to use this search last to first I can't mention But the data we have in that data sheet is ordered from the oldest to the newest state So obviously if you want to find the first date, we can search first to last But in our scenario because we want to find the last day or so the most recent day We can search last to first. So all we need to do is click this option here Or we can just type in the minus one where it was easiest Close your brackets hit enter and then you can see we've now got that last value for Brooke So we can now see that he or they were originally in sales and then now from the technology department You first copy that down For the majority of our other individuals if they are all the same department But we can actually now see that marcus was also a similar scenario where he was originally in human resources His actual current latest department is finance So we kind of covered off to here So the first benefit of using xlookup is obviously it's a lot simpler to use and it also allows us to Have flexibility of from where we pull the data So as you notice with these ranges and apologies for not touching this at the time But obviously all we had to do is reference where the names were and then reference where the value to return is And unlike vlookup, it doesn't matter if this return column or return array is to the left to the right Or miles away from obviously the the the look up array Which just doesn't matter. So really that more flexibility to benefit over the vlookup there The second one obviously is this current department Okay, as we mentioned the flexibility we have here is we're now able to search in multiple directions rather than going from Top to bottom we can go bottom to top and there's a couple of options there available as well But we're not going to touch them in this video We may be through another one in the future where it comes to use those other options for the search type But the last one we're going to be looking at is bonus. So how we can Obviously pull through the bonus relevant to the salary for each of these individuals So once again, we're going to enter xlookup And open our brackets We'll go to our name. No, not this name this time We want to go to salary because we're going to look for this salary in our bonus table Do our comma. Let's go into our data And it might help if I remove the filtering So obviously we'll of course an error is here. So let's just remove the filters there so we can see everything So once again xlookup Take the name comma Fill in our data So the first thing we want to do is we want to look for our salary. So I'll have to done that again. What am I so third time lucky? Here we go equals xlookup And we want to look up the yearly salary so b5 We then want to look for that salary in this first salary column of our bonus scheme So we're just going to select all those values there And I hit f4 to lock that range The value or the return array so what contains the the value which return is this bonus column So we're just going to select those again and once more hit f4 We'll do a comma obviously get to that if not found we'll leave that blank We'll leave the match mode So it's a match mode we now want to use So what we have here is by default if you don't enter the match mode It just does default to zero and does an exact match So it's only going to find or return a value that is an exact match to your lookup value In this scenario because obviously we're dealing with salaries and obviously our salary could be Higher or lower than each one these brackets. So looking at 100,000 you can see just below the box here The salary might be say 101,000 In that scenario we want it to go back to the 100,000 threshold. So obviously if it's less than 125,000 We wanted to go to 10% bonus if it was 125,000 or over then it would go to 12% So in order to do that we have two options. We could either go exact match So it's going to find an exact salary match or the next smaller item So obviously it's going to go back to the salary If it can't find our salary exactly it's going to go to the next salary in our table While our lookup array that is less than our salary Or the other option we could do is again if there's no exact match it will go to the next largest salary available But for our criteria we want to go for an exact match or next smaller So what we do is double click that or again you can put minus one Not worry about the search mode. So we can just do a close brackets here hit enter And we can see the value has come through So we'll just copy that through one more time and we can see we've now got our bonus percentages in there as well So let's just quickly test a couple of these. So we've got kodi. So we've got 77045 So let's look in here. So 77045 so it's obviously between the 75 and the 100 But because we want it to be so 77 is not available in our range here So what we want to do is that 77 to revert down to the value the next smallest What is 75 to return 8 percent? So we can see yep, that has done exactly that We've got a 10 percent one here. So we've got a thousand 100 thousand and 945 So obviously that's just just short well 945 Over our 1000 pounds in it. So a thousand 100,000 here is 10 percent But obviously they're more than 100,000, but they're not quite 125 Therefore we wanted to revert that to the 10 percent assigned to a hundred thousand Pounds so you say bracket And that has done just that So we've populated all these fields and obviously gone through a number of scenarios And the different benefits of using the x lookup function. So thank you very much for watching that tutorial We've gone through a number of scenarios of how the x lookup formula is More beneficial and maybe easier to use should we say than the v lookup function Appreciate because we've been through quite a lot there if you do have any questions Please do drop a comment below this video and I will get back in contact with you or respond to that comment As soon as I can if you did enjoy the video, please do give it a like Not only is it greatly appreciated by myself because it shows me the sort of content You'd like to see a lot more of but also helps with that youtube algorithm to help Get this channel and this the content out to as many people as possible And lastly if you're new to the channel or if you've seen a few of our videos before Please don't forget to subscribe to the channel and hit that bell notification button Obviously it's going to help notify you of all of our future videos that do come out So lastly, thank you very much for watching and we shall see you in the next video Before you go, don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always don't forget to subscribe and hit that bell notification button