Excel XLOOKUP Function Definitive Guide

the first powerful function I learned back in my counting days it was vlookup it gave me a sense of power and cemented my love of excel but how I wish I'd had the fortune of the new X lookup function back then X lookup is everything filo copies and much more for example X lookup can look up to the left it won't break if columns are inserted or deleted in the lookup array X lookup can find the last occurrence of value it defaults to an exact match so new users won't accidentally return erroneous data it can return a range of cells or a single cell just like index and X lookup allows you to specify an alternate value if the lookup value is not found so there's no more need for if error now with all this new functionality comes more arguments but before we take a look I want to point out that X lookup is currently only available in office 365 on the inside of channels but everyone can use it in Excel online ok let's take a look at the syntax in the file for this video which you can download from a link in the video description you'll find the syntax information now don't be put off by the number of arguments in this function because most of the time you'll only use the first three and it's still way easier than vlookup now I won't dwell on the syntax here because you'll pick it up as we look at the examples and you can always refer back to this file if required scrolling down on this sheet you'll find links to each of the examples that are going to look at in this video ok let's dive into the examples we'll start with the simple X look up which is example 1 now here I want to look up the product caps in the product column and return the sales amount now back in the day of vlookup we would have said lookup caps in the product column and I want to include the sales column that includes the column that I want to return so the column index number is the second column of my table array and I need an exact match so I need to also enter a 0 for false close my parentheses and there's my vlookup with the new X lookup which is a simple typing of Excel and tab and we have it we're going to look up caps where are we looking it up in the product column what do we want returned the sales value and all I need to do is close parenthesis and press Enter because unlike vlookup X lookup defaults to an exact match so I don't need to specify it and I press ENTER now the bonus with X lookup / vlookup is if I insert a column in C vlookup breaks an X lookup is fine we can see the syntax of X look up there okay let's dive into the next example X lookup can also do an H lookup so Excel tab what do we want to look up we want to find the value for October where are we looking it up in this room here what do we want returned the value in this row here and press Enter and there's our equivalent H lookup and again here we can insert a row and our X lookup doesn't break now something to point out here it's important that the number of columns in the lookup array and the return array are the same if you have a different number of columns you're going to get a value error and this is the same with the vlookup example here we need the same number of rows in the lookup array as the return array in fact these rows don't even have to be on the same rows they just need to be the same size of course if they're offset in this example we're going to get the wrong information because for caps if I press ENTER we now pick up Jersey's value so you don't want to be doing that but it's just important to point that out that this number of rows is the same as this number of rows and in the hlookup example the number of columns for the lookup array and return array must be the same alright let's crack on to example number three the next function or functions in this case that X lookup can replace is index and match now this formula was crafted by mr. X himself aka bill Gellin so here I want to look up the value for clothing in February so it's this figure here so first of all we want Excel tab we want to look up February in this series of rows and the returned array well we need column C so it can use another X lookup to look up clothing in this row here and instead of the returned array being one row like H lookup it'll be all of the rows for Jan to December and I'll close parenthesis on my second X lookup and again on my first X lookup and you can see it returns the correct value let's take a look under the hood at what it's doing here so if I open the evaluate formula dialog box and I step through the evaluation you can see it evaluates the second X look up first so it's looking up clothing and you can see there that it returns a range it's returning c2 through to c-13 and it's excellent cops ability to return a range as opposed to just a value like vlookup does that allows us to use it for the return array argument now that all said I still think it's probably easier to use index and match so we want to index this range here we want to find February's row from this series of cells and we want an exact match and then we want to match clothing in this row here and we want an exact match close my second match close index and we get the same value I think the index and match formula is more intuitive but we can actually go one better so again index we want to index this range here and for the row number argument we're going to use the new x match yes there's another X function that's new and the main difference between X match and match asides from an extra argument is that it defaults to an exact match and that allows us to skip one argument in the formula so again going to look up febri here and I can just close X match because it defaults to exact so don't need the zero like I do up here to specify an exact match next we want to look up clothing in this row and close parentheses and again on index and then we have index and X match so although X lookup can do index and match I think you're better off using index and X match I think it's more intuitive for our users but it's up to you to choose at the end of the day all right example number four now we've got a few to cover here one of the things I'm asked regularly is can vlookup return multiple columns and yes it can but X lookup does it way easier so let's see Excel tab we're going to find helmets and here we want to return the sales amount and the average rating so we're going to look up the product and the return array is two columns all I need to do is close parentheses and press ENTER and you can see it's returned the average rating let's format that as a percentage and you can see it's spilled and we can tell it spilled because it has this blue border around the two cells what if we want it to return to non continuous columns well unfortunately we can't just add them with an ampersand we can however use choose and I need to thank when Hopkins for coming up with this idea so let's take a look equals Excel tab we're going to find helmets in the product column and return array well we're going to use choose so put two columns I need an array of one and two and then the first one is going to be the sales and the second one is going to be the category so you can see they're not contiguous in fact one is to the left we're going to close choose close X look up you can see now our formula spills and we've got our sales value and our category now we can also have X lookup spill vertically so in this column I have a filter function that returns a list of the products that relate to the clothing category so I can ex look up the whole filtered array and you can see it puts in the hash which tells me this is referencing the dynamic array returned by filter that starts in cell f11 so I'm going to look up all of those values where are we looking them up in the product column what are we returning the sales value so close parentheses and press Enter and you can see it spills but it would be really nice if I return the average rating at the same time so let's include column D in our return are a and a press Enter but it doesn't spill now currently there's a limitation in the Excel chalcogen that says X lookup can only spill the first multi value array in this case it's spilling down and it's going to ignore the multi-column result in the return array so what's the workaround well one solution is just to write two separate X lookup formulas so we can have it spill vertically going to look up these values and we're returning sales and press ENTER and if we make that absolute and that absolute and copy it across I'm going to just paste formulas and now we have our two spilled arrays but we need to separate X lookup functions so it's not really ideal and if I copy that down you can see the formula for the sales column the other option is to use this type of spilled formula so X lookup bib shorts going to look it up in the product column and we want to return both the sales and the average now I'm going to absolute that and absolute that press enter and then all I need to do is copy it down so again I've got one formula for each row so there are a few different approaches at the end of the day it's a little bit more work than if this just spilled correctly but really in the scheme of things not a major shoo personally I would choose this version of the formula because it's going to dynamically update as my filter array adjusts alright cracking on to example 5 another thing we can do with X lookup is create a dynamic range or a dynamic named range remember I mentioned that X lookup can return a cell range as opposed to just a value from a cell which we can do with vlookup so let's take a look here I want to sum the sales between the first of July and the first of December my dates are day month year so for those of you in the u.s. they're going to look a little wonky so here we're going to X lookup now I'm looking up the first date in the date column and I've format of my data in a table so you're seeing the structured reference here for the table the return array is the sales so this is going to return my first cell reference it's going to return this cell here d8 I then insert the colon range operator the last cell in my range is also going to be returned with X lookup this time we want to find the end date again in the date column and we want to return cell containing the sales value so I'm going to close parentheses and remember I want to sum the sales so we need to wrap it in the sum function and then close my parentheses so we've got the first cell reference and the last cell reference in my range and the sum will sum the values in between so press Enter and you can see it returns a hundred and thirty thousand so let's just take a look at how this is evaluating if we evaluate stepping through the first X look up you can see it returns cell d8 and then we're going to step through the second X lookup and it returns cell b13 so we no longer have to use index a match or offset to create dynamic ranges or dynamic named ranges instead we can use the simpler X lookup function and I've inserted some data validation here so I can change the date range and you can see my formula is dynamically updating it's a pretty cool lots of uses for X lookup so far I'm up to example five and we've only used the first three arguments all right let's look at example number six back in the early days of vlookup we used if is n a and then vlookup to handle errors then came along if error which made life much simpler and more efficient for Excel but with X lookup we don't need any other functions you'll notice that the fourth argument is if not found and in there we can specify some text or a number or another cell or even a formula if the value that we're looking up isn't found so here I want to look up locks and you'll find it's not in the list of products and we want to return the sales amount and if we don't find it I want to say not found close my parentheses and press Enter and you can see it returns the text not found I don't get any ugly errors of course if I didn't specify an argument for not found I'm going to get the hash n/a error and you might actually prefer that because it might be important to you to see those errors but in this example we're going to leave it with the text not found all right cracking on to the next example one of the arguments in X lookup is the search mode and this tells X lookup what direction to search in so let's take a look I want to find the last sales value for clothing so I'm looking up clothing in the category column and I want to return the sales value now I'm not concerned if it returns me in that error so I'm going to skip the if not found argument I want an exact match that's the default so I can skip that as well next I have search mode by default X lookup such as first two last but I want to find the last record for clothing so I want minus 1 for search last two first so I'm going to tab 2 into minus 1 close parenthesis and you can see it's found seven-and-a-half thousand which is this record here for jerseys you can see it's the last record for clothing now if we take a look at these arguments again you can see that the search mode also has binary search and that requires it to be sorted in ascending or descending order now in earlier versions of Excel binary searches evaluated more quickly but according to Microsoft in office 365 this is no longer the case as a result there's really no significant benefit to using the binary search options and in fact it's easier just to use one or minus one because they don't require the table to be sorted all right let's move on to the next example and one of the limitations of vlookup is the inability to return values to the left of the lookup column now X lookup is not hindered by that limitation for example here I want to return the category from this table here so I'm looking up the product in column G and I want to return the value in the column to the left so Excel tab I'm looking up by cracks in column G and I want to f4 to absolute that and I'm returning the values in column F again f4 to absolute that now close my parentheses and ctrl enter to enter the formula down so you can see how easy it is to look up to the left no more complicated jumping through hoops you don't need to even use index and match although that would be the next simplest that said there is a workaround with choose that tricks vlookup into looking to the left and I'll include a link to that in the file for this lesson all right cracking onto our ninth example now vlookup supports wildcards for partial matches by D fold and that meant looking at words that contained a wildcard like an asterisk would require the wildcard character to be prefixed by the tilde and the tilde for me is in the top left of my keyboard that's a tilde but with eggs lookup we need to specify if we want to use a wild-card so let's take a look here I want to find the first instance of any product that contains the word bike so for example this bike with an asterisk either side could look up a word like mountain bikes or even just by let's take a look so we're going to look up the value in f1 in the products and we'll return the sales and if not found well that's fine it can return an error the match mode here is where I select the wild-card so let's tab to select to close my parenthesis and press Enter so you can see it's found the first instance of bike which is bike racks and it's returned the sales amount all right cracking onto the last example one of the common uses of vlookup is to find an approximate match so here I have my salespeople their sales for the period and I want to work out what percentage Commission they earn now the Commission percents are determined based on the sales values so I'm going to look up their sales in this commission table and return the Commission percent for example Burton he's sold fifty thousand five hundred he should be getting twenty five percent commission donaldson he sold thirty six four hundred it's not quite 40 so he meets the thirty thousand Commission bracket which is fifteen percent so typing in Excel tab we're going to look up the sales value let me just drag this out of the way in the sales column now I'm going to f4 to absolute that and then we're going to return the Commission amount and I'll f4 to absolute that now I'm going to skip the if not found argument and here's where we use the approximate match so I want to find an exact match or the next smaller item so if they haven't met the sales bracket they fall back to the previous bracket alternatively I could use one to find an exact match or next larger item but in this example I want to find an exact match or next smaller item so we want minus one going to tab to accept that closed parenthesis and control enter to enter it down now notice that unlike with the vlookup approximate match my table here is not sorted I've got zero after 50,000 and that's one of the benefits of X lookups approximate match you do not need to sort your lookup table pretty cool huh so there you have ten handy uses for X lookup it's replaced the need for vlookup hlookup index a match offset and if error now I'm sure you'll agree this is a game-changing function so take a moment to download the excel file for this lesson from the link here or in the video description I hope you're as excited about X lookup as I am if you like this video please give it the thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 100,617

Rating: 4.9751859 out of 5

Keywords:

Id: 2ViMm-wuM3U

Length: 20min 48sec (1248 seconds)

Published: Tue Jan 07 2020