How to Use XLOOKUP in Microsoft Excel

hi everyone my name is Kevin today I want to show you how you can use X lookup in Microsoft Excel X lookup is a new function that Microsoft is recently releasing that helps you find things in a table or in rows or columns of data X lookup replaces the vlookup and H lookup functions if you've ever used those before I'm going to show you step-by-step how you can use X lookup I'm going to make it really easy so anyone can understand how to take advantage of this as full disclosure Before we jump into this I work at Microsoft as a full time employee I'm required to say that my mighty HR department anytime I type talk about Microsoft products now X lookup is a new function many of you have probably not heard of that before and Microsoft is slowly rolling it out it's currently available to office insiders but it'll soon become available to everyone if you're interested in getting on office insiders I'll show you quickly how you could join that and then we'll jump in and walk through how you use X lookup all right well enough talk let's jump on the PC and let me show you how you take advantage of it so here I am on my Windows 10 PC and I have the latest and greatest version of Microsoft Excel on my machine I mentioned I would show you how to join office insiders if you're interested in getting the latest version if you're on office 365 you'll be able to do this if you click on the file pivot up on top that brings up this backstage experience on home by default and what you're going to do is scroll down to account click on that and then over on the right-hand side you'll have the option of joining the office insider program and then you'll get the latest releases of office ok so let's jump into X lookup and how to use this and so I want to show just a very basic example of how you could use X lookup so just to orient you to the data let's pretend that I'm a college and I have all these students in my school so they have a student ID there's a name associated with a student you'll see that I'm a student in this school there's also a home state and there's a major for each of these students and what I could do is with X lookup I mentioned earlier that it helps me find values and so what I'm going to do is well we just do a quick example here so I'm going to type in equals that's how we always start a formula in Excel and then I'm going to type in X lookup so that starts the that starts the function and what I want to do is it asks me a few different things and we'll step through this one by one and so what I want to do is I want to look up let's say I want to know what Jack James I want to know what his home state is and so what I could do is I'm going to type in his name I'm gonna put it in quotes since I'm looking up a name and so what I'm going to do is I'm going to type in Jack James and then close it with quotes and what I want to do is I want to look up so wouldn't ask me to do is it says well what is the lookup array and so basically what that means is in this table what column has the value that I'm looking for so I'm gonna insert a column and then I'm gonna say well let's look at this column here so I can either select all these values or I could simply select column B so I just want to look in this column now I'm gonna insert a comma and it says return array basically what that means is when it finds the value that I'm looking for so when it finds Jack James returned me some other column and what I said before is I want to know the home state for Jack and so what I'm gonna do is that's in column C so I'm gonna select column C and what I'm gonna do then is I'm going to close the parentheses and hit enter and so what that does now is you'll see that it's looking at column B and it says hey let me look for Jack James it finds Jack James and then it says okay well then give me return me the value from this other column C and what I could do is instead of saying column C I could say well what's this what's Jack's major and what I'll do is I'll select column D and then I'll hit enter and what that'll do is it tells me that as majors English now one of the nice things about X lookup is that you can also get back values from the left of the value that you're looking for so what I'm going to do is instead of column D I'm gonna look for column a and then we're gonna hit enter and what you'll see is now it returned me Jack James's student ID now with vlookup that was a function that X lookup is replacing you could only go from left to right and so you wouldn't have been able to get the student ID unless the student name appeared first so this is a nice improvement of X look up over vlookup now one of the other improvements too is so here I'm returning for this one individual I'm getting one value back so whether it's the student ID the home state or the major but let's say I want to get multiple pieces of information back so what I could do is let's say I want I want to find Jack James but I want to know his home state and his major so what I could do is I'm gonna select column C and D and then I'm gonna hit enter and here you'll see it returns both Hawaii and English and so I can get two values back this is another nice improvement over vlookup which was the previous function that would return values to you so this is a really nice way to look at tables and to get data back now one of the really nice things and so you might say well hey I could just look for James Jack James and I could see that information right there how is this valuable well let me show a quick example of kind of the great value of formulas in Excel and and that's when you feel value so here let's say I have another sheet with all these names and someone asked me hey fill in all the student IDs well what I could do is I'll say hey X lookup and it says lookup value I'm looking for Shawn Reagan and then I'm gonna hit comma and it says well we're you gonna look for his name well let me go back to the previous sheet and I'm gonna look in column B and I want to return the student ID so that's a return value so I'm going to select that and then I'm gonna close the parentheses and hit enter so you'll see now for Shawn Reagan they caught me his student ID nothing special there we did that in the previous example the only difference is here I'm on a different sheet but now what I could do is I could simply drag this down and you'll see for every person named on this worksheet I just quickly got the student ID back so it looked up each person's name and it gave me the ID back so that's a very quick way to look for or find data and then incorporate it into say another sheet or another place where you need some value from another sheet and work at Microsoft this is something that I use all the time and it's been something that's been very valuable for me at work ok so we just ran through a basic example of X lookup what I also want to show is there was another and called hlookup and what hlookup does is it's very similar to what was vlookup but it allows you to look for data when it's oriented in a horizontal way so in this previous example all of this data was oriented in a vertical way where you have the column headers and then the data going down in this example I have the column headers or what are now row headers as rows and all the data goes across and so what I could do is I'm gonna type in X lookup and why don't we just find we're gonna look for let's say Bill Barry and what I want to do is so I'm going to look for him in this row and then perhaps I want his major back and so then I'm gonna select this is the return row so when it finds bill Barry it'll get me computer science back I'm gonna close the parentheses hit enter in here tells me computer science so X lookup can both work across data when you're looking vertically as well as data when you're looking horizontally it works just as well so pretty nice there that's what you previously could use H lookup for X lookup now replaces that so very nice now what I'm going to do here is I want to show another part of the function so when I click into the function here you'll see that not only do you have the lookup value then look up array that return array this is stuff we've already been talking about but you have these other things like if not found match mode and there's also something called search mode so let's jump in and see what some of these options do so here what I've done is let's say that I'm looking for let's just make up a name Liz let's say Benin and Liz Benin is obviously not a student at our school and so I'm looking for that name and then I'd look in column B and if I find that I returned her major so I'm gonna hit answer and it just gives me an n/a because she doesn't exist as a student so I'm just gonna hit a comma here you'll see the next part of the formula says well if not found what do you want to do and in this case I'm going to insert quotes because I'm going to type text and I'll say student does not exist and then I'll close the quotes and just hit enter and so what this does is if it doesn't find a student by that name it'll simply return this text instead instead of showing me that n/a so this is a little more user-friendly doing it this way so I kind of like that and I'm gonna stick with that so here student does not exist okay so this is great the next thing that we're gonna do is I'm gonna look at the next part of the formula so this is getting a little more advanced and using a little few more of the features if you just want to do a basic X lookup you should be good already with the previous instructions so here on match mode what I can do is let's say that someone's income is forty-five thousand eight hundred seventy six I want to find out what is the effective tax rate for every extra dollar made beyond this and so you can see that while you know it's not in this group the the income here exceeds thirty nine thousand four seventy-five and so for every additional dollar this person is going to be in this tax bracket or the twenty four percent because it's more than the twenty two percent rate but it's less than eighty four thousand two hundred so here to X lookup can help us so let's type in X lookup and what we're gonna do is we're gonna look for this income so I'm going to select this and what we're going to do is we're gonna look for the income in column B so I'm just gonna select column B and then I want to get the tax rate back okay and if I just hit enter now what you'll see happens is well there's no income of forty five thousand eight seventy six in here it only sits between these different items and so what I'm gonna do now is we'll just continue the formula and here it says if not found for now we're not going to fill that in so on the sensor no another comma and here it says match mode that's the next item in the formula and you have a few options by default it's set to exact match and there was no exact match so it returned nothing back well what I could say is well hey search for this number and if you don't find it give me the smaller and the next smaller item back which would be thirty nine thousand or give me the next larger item and that's what I want because if it doesn't find forty five thousand well I want the next one up and so I'm going to select this one here and then I'm just gonna go ahead and hit enter and so what that does now is it looks for forty five thousand it doesn't find it and the next one up is eighty four thousand two hundred and the tax rate associated with that is twenty four percent let me just throw the percent sign in there and so there are twenty four percent and return me the tax rate so another cool way to use X now what I want to do is this is now getting very fancy of how you could use X lookup but you could do nested X lookup now this is kind of like in the movie Inception where you can have a dream within a dream and that's what we're gonna do here with X lookup we're gonna have an X lookup within an X lookup and what I want to do is I want to know what is the profit for quarter two well quarter two obviously is 170 for 804 but I could use X lookup to get that and I'll show you how this is really cool as we go through it what I'm gonna do is I'm gonna type in X lookup in here and what we're gonna do is we're looking for the profit so I'm gonna go ahead and select profit here and what I want to do is I want to look for it in this set okay and now for the return value what I want to do is this is gonna be another X lookup function so we're gonna type in X look up again and now what I want to look up is I want to look up the quarter so I'm just gonna select that as the quarter over here and then where I want to look that up well I want to look that up across the top so across this header here and then in terms of what it's going to return I'm gonna select all these values so I search for both this column and I search for these rows and I search in these columns and then I'm gonna return some value within this table and then I'm just gonna go ahead and hit enter and what this did is so it's saying hey for the profit and quarter two it's 170 for 804 now what's nice about this is what I can do is instead of profit I could say will tell me the cost and now it tells me the cost or I could say you know so that the cost tell me the revenue there's the revenue and I could even change the quarter and I get throw in quarter three and you'll see that very quickly depending on what I'm looking for it'll very quickly return the result so using X lookup can allow me to find data very quickly whether it's just looking at rows or columns or whether it's looking at a combination of rows and columns okay well that's a very quick tutorial of how you could use X lookup and some of the Grateful action allottee that's coming soon to everyone in Microsoft Excel to take advantage of this you'll need you'll need a current version of Microsoft Excel if you have office 365 and you're an insider you can start taking advantage of X lookup today if this video helped you learn how to use X lookup please give it a thumbs up if you want to see more videos like this in the future please hit that subscribe button that way you'll get a notification any time new content like this comes out and lastly if there are any other videos that you want to see me cover on this channel please please leave a comment down below I read all of them and I'll add it to my list of videos to create in the future alright well that's all I have for you today I'll see you next time bye

Channel: Kevin Stratvert

Views: 58,458

Rating: 4.9674797 out of 5

Keywords: xlookup, excel, tutorial, office, microsoft, data, analyze, vlookup, office 365, excel 365, excel 2019, excel 2016, excel 2013, excel 2007, excel 2010, microsoft office, microsoft excel, help, how to, guide, step by step, formulas, formula, function, functions, hlookup, x lookup, index, match, 365, 360, lookup, example, rows, columns, row, column, not found, fill, horizontal, cell, vertical, nested, match mode

Id: xDUlDhPv1RE

Length: 13min 38sec (818 seconds)

Published: Sat Jan 04 2020