New Xlookup Function: A Vlookup Comparison

Microsoft just released a new lookup formula called X lookup and in this video I'm going to explain everything you need to know to get started with it and why it's such a cool alternative to vlookup or index match [Music] all right so I won't make you wait any longer here is the new EXO clip function in all of its glory and in this video I'm going to explain what X lookup is and show some of the new features that vlookup and index-match don't have I'm also going to compare X lookup to both vlookup and index match I will then share some of the pros and cons of X lookup I hate to say that there are cons but there are definitely some things that you want to be aware of that will help you prevent errors and also write the formulas faster and then finally I'm going to explain the general availability and what version of excel you need to get X lookup so let's go ahead and write an X lookup formula and that'll be the easiest way to learn it I'm going to type equals here and then X you can also see there's a new X match function I'll explain that in another video but another great new function for us so here we have X lookup so I'll just start typing X lookup you can see here that it searches a range or an array for a match and returns the corresponding item from a second range or array and by default an exact match is used so let's tab into this and here we have the arguments for X lookup now there's five arguments for x lookup but only three of these are required so when you first see this it might feel a little overwhelming but actually the first three are the only ones required so the first one is going to be the lookup value this will be similar to vlookup or index match we have our lookup value here and then we're gonna type a comma and then of course the range we want to look in this in this case we're only going to specify a single column so I'll hit control shift down over there to select that I'm gonna hit f4 on the keyboard to make that an absolute reference type a comma and then the next argument is the return array so here is the range that we want to return a value from so in this case I want to return the email address from the matching name so I'm going to again select that so ctrl shift down arrow there hit f4 on the keyboard to anchor that and that those are the only arguments that we need the only required arguments as the definition of the function stated an exact match is the default mode here the default match mode so we can just close the parentheses and hit enter that will of course look up the name in column E and return the email address from column G and exa cup works the same way vlookup and match do it's going to look down the lookup range here until it finds you lookup value and then it's going to return a value from the return range in the same row now I'm sure you're curious about those other two arguments so let's check those out have F two to jump into this put our cursor here and type a comma we can see the next argument is match mode and here are our options for match mode so we have an exact match and again this is the default so we don't need to specify this if we want to do an exact match we then have negative one which will do an exact match or the next smaller item we have one that will do an exact match for the next larger item and then two we'll do a wildcard character match so I have examples for these and I'll show those in this video but for now we can just specify a zero here for an exact match then I'm going to type a comma and the next argument is search mode so this allows us to specify the search order and the default is one here for searching first to last so in this case from top to bottom you can also specify negative one to search from last at first which would be bottom to top and then you get there's also some advanced options for binary searches we won't necessarily focus on these in this video maybe we'll do a future video on that but this would be for some more advanced use cases where performance is really important to you so for now I'll just specify it one and then again we can hit enter that's going to return the same result because we used those default parameters or the default arguments here and typically if we wanted those default arguments again we would not need to specify those we only need three arguments for X lookup so we'll now look at some additional examples of X lookup and some of the cool new features first of all it can do a horizontal lookup as well so in this example here we're using X lookup for a horizontal look typically you might use H look up for this or even index match but X lookup can do this as well so here we're just looking up this value of March in this row right here and returning the value from this row here for the matching column so that's what we see here we have the look up array or that look up row and then the return row would be row 7 hit enter and we'll get 1949 which is again this matching value right here another very cool feature of X lookup is wildcard characters so in this example here I have a column of last names and I want to look up these last names in column e over here but in this case have the full name in column E and then I want to return the email address so this is absolutely possible with X lookup using wildcards so we'll jump into the formula and for the fourth argument here I've specified a two for the match mode I've specified it to hit alt down arrow here you can see those options again and again to allows us to use a wild-card character match and we do this within the lookup value so for our lookup value you can see I'm specifying cell b4 but I've joined that with the ampersand with the asterisk so here in quotation marks I have the asterisk symbol and that's just allowing us to say any characters any number of characters before the value of cell b4 will be part of our lookup so we'll do lookup for this name over here and then anything before it and then once the X lookup finds that it will return the match the matching row from the return array and we could use both the asterisk and the question mark for single characters so down here I have an example of that and here you can see I put a question mark here for this character the third character because we're looking for this name here Briana but maybe sometimes it's spelled with an i instead of a y or something like that so we want to make sure we account for both of those scenarios and this will absolutely do that and then my X lookup function looks the same here we're just specifying b13 so we don't put the wildcard character in here because it's in the value over here and then again we have a two for that fourth argument for the match mode so that's just a really brief overview of how to use wildcard characters with X lookup I'll do a whole nother video on this that explains it in more detail but hopefully that just gives you a little taste of what's possible with X lookup another awesome feature of X lookup is the search order so in this example I want to answer the question which is who made the last sale on this date so this X lookup formula is doing that it's looking up the date in column B and then it's returning the salesperson name but for the fifth argument here the search mode argument I've specified a negative one to search from last to first so this is going to search from bottom to top instead of top to bottom and once it finds the first value the exact match because in the fourth argument here we're specifying a zero for exact match so once it finds the first exact match of this date right here from the bottom going up it's then going to return that sales persons name right here and then I also have a similar X lookup formula to return the order ID right here using the same basic formula and for the table row I've used the X match function as well so that's another alternative to X lookup X match which is going to return the row or column number instead of the value from the cell and again I'll do a separate video on that but it kind of cool to see that one in action as well has the same additional arguments here for the match mode and the search mode as X lookup so let's take a look at how X lookup compares to vlookup and index match Microsoft is calling X lookup the replacement or predecessor to vlookup an index match so we'll see how they compare first of all in this example we'll take a look at a comparison to vlookup and I believe X lookup has three main advantages over vlookup so here we have a formula on X lookup formula that's looking up this name in column F and returning the email address from column H and that first advantage is that only three arguments are required again we have that lookup value lookup array and return array so we'll only need three arguments in this cell below I have a vlookup formula that's doing the same thing returning the same result again we have our lookup value here we have our table array the column index number and then we have the fourth argument which is range lookup since this defaults to true we always have to specify a false or a zero here so it actually requires four arguments one additional over X lookup so I think that's the first win now the next win is inserting and deleting columns so if I was to insert a column here before the email column we can see now that X lookup is still going to return the correct result the email address and that's because we've specified a separate array for the return array our vlookup breaks in this case because we have a hard-coded 3 here for the column index number and the third column is now the phone number instead of the email address so we need to do something here and add an extra function like the columns function to make this more dynamic and that just requires extra work and so therefore the X lookup wins in this case when we're inserting and deleting columns within that table array and then finally X lookup can also look to the left so on this sheet we have an example of that now we're looking up the name in column H and returning the email address which is in column F which is to the left of that name an X lookup can do this because again it has a separate arguments for the lookup array and return array vlookup cannot do this yeah you can kind of hack vlookup with the choose function to make this work but in this case you would just use index match instead but here you don't have to you can use X lookup and keep it all in one function and then I also wanted to point out that X lookup can do closest match as well so here's another comparison to vlookup in this case we're have a commissioned table here where we're looking up commission rates X lookup can do this comparison by specifying the fourth parameter the match mode as negative 1 so that's going to be an exact match or next smaller item so this is similar to if we were to do a vlookup and have the last argument as true for an approximate or closest match so the point here is that X lookup can do this as well when we're doing approximate matches and X look up actually has an advantage here because it can do different match modes so it can go exact match or smaller or exact match or larger so that means you can actually set up your lookup table here in different ways we're currently looking up the minimum but you also look up the maximum as well with ex look up so now let's compare ex look up and index match in this example I have the same formulas here's our ex lookup formula and down here we have the index match formula now with a basic index match the real advantage of ex lookup is just the ease of use we only need to use one function in our formula with index match of course we need to use two when we write it it's a little bit backwards because we have our return array first then we have our lookup value here and the lookup array so X lookup is just easier because it's more simplified we just have one function it goes in order from lookup value lookup array and then return array so much easier to write and of course index match shares those same advantages of being able to look to the left and also inserting and deleting columns so that's why it's better than vlookup however X lookup you could say is even better because you can do all that in one function and then of course X lookup also has these additional arguments for the match mode where we can look for a wildcard character really cool feature and the search mode where we can look in reverse order and I should also point out that you don't have to specify the match mode you can just leave it blank after that comma there and then if you wanted to do a reverse order you could do a negative one here so this formula will still work as well where you just have a blank for the fourth argument there or you don't specify anything and again that defaults to zero for an exact match okay so now we'll take a look at some of the pros and cons of X lookup so I've mentioned a lot of the pros or advantages throughout this video so I'll just go through these quickly as we set it default to an exact match so we don't have to specify that last argument like we do with vlookup it only requires three arguments which is great it works both vertically and horizontally so we don't necessarily need H lookup anymore either there's just one function instead of two if we were using index match we have that fourth argument for wildcard characters which is really cool the fifth argument allows us to do a lookup in reverse order which is also an awesome new feature and then it returns a range instead of a value that's more of an advanced thing with nested formulas I'll cover that in a future video so for potential cons or drawbacks the first is that the additional optional arguments can make the function look a bit overwhelming so as I mentioned only the first three arguments are required and then we have these two additional arguments that are optional so for new users to excel or someone that doesn't use excel very often this might look a little bit overwhelming so if you're teaching X lookup to your co-workers or friends make sure that they know that only the first three arguments are required and that's all they need to do an exact match lookup another drawback is if the lookup and return arrays are not the same length this is probably the most common air we're going to see with X lookup so I have an example here in this sheet and this is an X lookup formula and we can see here that the lookup array goes to row 1003 and the return array goes to row 1002 so they're not the same length here and therefore this returns a pound value air so this is going to happen a lot whether using the keyboard to select the ranges or the mouse if you're using the mouse and maybe you don't go all the way to the bottom you're going to get that air if you're using the keyboard shortcuts and this column contains blanks then you might have to find ways to make sure you select that entire column so one good alternative there is to use excel tables and reference the table column names with structured references I'll do another video on that as well but that would be a good way to help make sure that you don't encounter this air where the ranges are not the same length and I'll also show how my free vlookup assistant tool can help with this as well so another potential con is it can be time-consuming to select both of those ranges so if your formula is referencing thousands of rows like we have in this example here if you're using the mouse to select these ranges for the lookup array and the return array that can be very time-consuming to select those again my vlookup assistant can help with that and I'll show that in just a second and then finally we have to remember to make both of those lookup and return ranges absolute if you're gonna copy the formula down again it's that extra step of hitting f4 on the keyboard to make sure both of those ranges are absolute so I'll just quickly show how vlookup assistant can help with this probably going to need to change the name at some point as X lookup becomes more popular but this is a free app that I've created I'm eventually going to turn it into a free add-in right now I just have some buttons up here that'll run some macros and I also have a previous video that explains this in a lot more detail but this just allows us to create formulas so if I wanted to create an X lookup formula here to return the email address just select the cell where I want the formula to be I'm going to click the X lookup button that's going to ask me to select the cell that contains a lookup value also defaults to the one to the left so we'll just hit OK and then we're going to select a row that contains both the lookup value and the return value just a single row we don't need to select everything here so I'll just select that row starting with the lookup value to the value of on a return hit okay that's going to in create the formula and insert it in the cell here as you can see it's done all that work for us so it's found the length of those based on the used range over here it's made those absolute references as well and created the entire formula with just a few clicks so let's make it faster and easier to write formulas and prevent errors and again this is a free tool I'll put a link in the description below this video where you can download this and check this out also has features to convert existing vlookup formulas to index-match or vlookup or index match to the new x lookup and so all that can be done in one click instead of having to rewrite your formulas and then finally on our pros and cons list our 2d lookups like index and match can do so I'll just show a quick example of this as well here in this example we have an X lookup that again is looking up horizontally but if we want to do a both a horizontal and vertical lookup in the same function X lookup can't really do that by itself you can use X lookup combine it with another X lookup or some other functions to make that happen however vlookup can do that by itself when you put a match function in it you nest a match function for the column index number it's now looking up both the vertical lookup here for the region for South and also horizontally across for March to return that result then of course we can do that with index match as well if we use to match functions here for both the row number and the column number we can use index match for that to make more of an interactive model here where we can potentially make changes here selections to see the result so X lookups a little bit limited on that if you want to do a setup like that and index match is probably your best bet and of course you can use the new x match function with index as well now in terms of availability you're going to need an office 365 subscription to use X lookup here's the help page for X lookup and you can see what versions of excel that's available on right here so you'll need office 365 it'll also be on excel for the web and the excel web mobile apps it will not be available on office 2019 or any earlier versions nor will there be any backwards compatibility so if you're on office 365 and you're using X lookup any users that you send your files to will also need to be on office 365 now currently X lookup is only available on the insiders fast Channel for office 365 but it will be rolling out to the other channels in the next few months now the insiders fast is a free program and I'll put a link in the description below this video where you can get access to it X lookup is currently only available to a portion of those insiders fast users but hopefully by the time you're watching this video it will be rolled out to everyone on insiders fast so that's an overview of the new X lookup function I hope you've enjoyed it and I hope you're excited about this new feature of Excel and I now have two questions for you the first one is what do you think will this be easier to teach and explain to your coworkers X look up instead of vlookup or index match is this something you're excited about and the second question is what questions do you have about X lookup I showed some of the features like partial matches with wildcards and doing a lookup in reverse order but there are a ton of other solution and things we can use X lookup for so I'm just curious to know what else do you want to use X lookup for so leave a comment below with answers to either or both of those questions I hope you're excited about X lookup and I look forward to seeing your responses thanks again for watching have a great day and I'll see you in the next video [Applause] [Applause]

Channel: Excel Campus - Jon

Views: 168,516

Keywords: excel, excel campus, xlookup, xlookup in excel, vlookup in excel, xlookup vs vlookup, xlookup vs index match, excel lookup value, excel lookup wildcard text, excel lookup partial match, excel lookup reverse order, excel xlookup reverse order, xlookup value error, #value error xlookup, xlookup tutorial, xlookup explained, xlookup for beginners, xlookup everything you need to know, excel xlookup, xlookup versus vlookup, xlookup formula

Id: pURSha68Mjs

Length: 20min 55sec (1255 seconds)

Published: Wed Sep 04 2019