Excel XLOOKUP Function – Comprehensive Lessons – 27 Examples - Excel Magic Trick 1600

welcome to xl's ultimate lookup function X lookup now wait a second vlookup one of the most popular excel functions in history the ones that all of the employers ask us about we're not going to use that anymore we're gonna have to learn X lookup well the good news is it does so much more than vlookup and if you can believe this it's much easier to use you are not gonna believe what X lookup can do it does exact match by default when you do approximate match you don't even have to sort the table it can find the last value when there's duplicates it can lookup left it can do horizontal or vertical lookup or both at the same time it can look up the whole row the whole column return multiple items and much more now X lookup is only an office 365 Insider and it will replace much of what we've done in the past with vlookup hlookup match index and lookup does that mean all these functions are dead well no there are still uses for these and we'll see later in the video but most of the uses for all these functions we can wrap them all up into one function X lookup now there's 27 amazing examples in this video so be sure to check out below the video there's a time hyperlink table of contents and of course you can download the excel workbook and follow along now on the X lookup an X match sheets there are some notes including screenshots from some of the new arguments in these functions you can come back and look at these notes later we'll go through all of these exciting new options in our examples we're gonna start on the sheet one two three now we're gonna start with exact match lookup I need to look up quad in this first column here and then jump over to the fourth column to get the price and bring it back to the cell here's the old method Envy look we had a look up value the table with all four columns we had to list which column have the item we want to go and get and bring back to the cell and look at this we had to tell it zero for exact match and guess what in vlookup that was not the default the default was approximate match the new way equals x el tab there are five arguments the last two are optional and we'll look at those later but for a basic exact match look up look up value while I'm looking up the quad , look up array that means only the column where we take that value and try to find a match , return array look at this we do not have to highlight all four columns just those two columns that means two things we do not have an argument to say which column has the thing I want to go and get and later if we insert a column it'll already know the correct column to try and go and get something from and the column it needs to match and for exact match that's it close parenthesis and enter so we didn't have to put a 0 for exact match like vlookup and if I come up here right click insert well of course vlookup is still looking at the 4th column but X look up that column price automatically updated to f3 to f6 so for exact match lookup that's quite an improvement lookup array return array we don't have to put the column index number or the 0 for exact match now how did Microsoft think this up equals L oo lookup this is the original lookup function all the way back to the first spreadsheet busy calc lookup value was the same but lookup vector and result vector back in the original lookup function they had it right separating the column we get a match and the items we want to retrieve into two different arguments now we only looked at the first three arguments now let's look at the last two arguments over on the sheet four five well we saw how to do exact match with a new X lookup function but how do we do a proximate match lookup we're going to use units and here's our approximate match lookup table the first column is sorted which is required for vlookup and match and the fourth column has our discount now some of the ways we used to do it in the we could use the original lookup function and actually I still teach this in some of my classes because this is the fastest way to do this if you really have a reliable table that's always sorted enter if we're worried about inserting columns we could use lookup that way and of course most people use vlookup notice that we're not using the last argument and vlookup because the default is approximate match lookup now here's how we do it with equals excel tab well we have a lookup value tamo and we get to split the column where we're making a match comma and the column that has the items we're trying to retrieve and bring back to the cell we get to split those apart comma and here it is match mode the default is exact match -1 means and this is brand new this option right here is what we now use when we want to use approximate match like vlookup now notice what it says exact match or next smaller item now the way they worded this is much easier to understand than the way the help use to describe it you used to say hey we're gonna take this lookup value and find the biggest value that is less than or equal to the lookup value so the biggest value in this column that is less than or equal to that is 48 so it gets 0.43 but now we don't have to worry about that we use this option for approximate match and it's much easier to understand it can't find an exact match in here when it's looking up 73 but we know it's right there and so it takes the next smaller one now again using any of these options in this fourth argument do not require that we sort the data set in any particular way so tab 4 minus 1 close parentheses and that's the new way that we do approximate match lookup now let's look at one other example well wait a second that's the same one we just did but look at this there's the fourth argument search mode first to last that's the default last first we'll see that one in a moment and look at this binary search that was the TEC chuckle term for the old approximate match right here sorted ascending order and descending order this would be like match using -1 let's just try this this is the old binary search tab close parentheses and enter now let's see what happens when we sort right click will sort Z to a ooh they all break except for the amazing new X look up with -1 ctrl Z and it doesn't matter we could have some crazy sort order and it still works ctrl Z so we can do approximate match and we never have to worry about sorting now we want to go to our next sheet and the lookup situation is we have a pipe and it's 11 inches but when we find a match in this column it's not exact match I need a proximate match but when we find an approximate match I need to find the biggest value in this column that is greater than or equal to 11 so the biggest value that's greater than 11 is 12 inches then I can retrieve land square footage that that pipe can be used for for drainage now how do we do it before X lookup well we use match and that was that very rarely used match type minus 1 but now we use Excel tab there's the pipe that's our lookup value comma trying to find a match there comma here's the values we're trying to retrieve comma and here's the fourth argument exact match approximate match here's approximate match but guess what it'll find exact or the next larger value now there's one other option here wildcard we'll see that later so when I hit tab close parenthesis we have looked up the correct square footage now let's go look at our next example and in this example we have a lookup situation where I need to find the last one well before X lookup we'd use an array formula inside of lookup that construction would always get as we copy down the last entered item for each product but with Excel tab it's so much easier unlock nope that comma within this range right here comma I'm trying to get the units comma Maximo we're gonna assume exact so I'm gonna leave it empty and there it is I can use the second argument minus one search last two first that means there's lots of duplicates here for Carlota and it's getting the last one now this option here does depend on how it's sorted in the worksheet but sometimes that's exactly what you want close parentheses control enter and copy it down I'm gonna go to the last cell and hit f2 oh I forgot to lock look at that f2 and I'll do the same trick as an earlier click on the screen tip f4 click f4 control enter double click and send it down go to the last cell and hit f2 now notice I forgot to lock and I got into trouble well if we're using X lookup that means we have the correct version why don't we just use equal unique I'm gonna get a unique list of items from that column this is a new array function when I hit enter because there's multiple items it spills f2 well what if I want it sorted I simply sort enter now this is a spilled array I can see the actual formula up there the cells below do not have the actual formula lookup value if I highlight all of them it puts the syntax for please look in the top cell with the formula but get the whole array comma I need to try and match in product comma get the unit's comma comma down arrow I want the last one tab close parentheses and because that's multiple items X lookup will know to spill alright let's go to our next example well I'm not gonna even do this one because we already did it if there are duplicates in lookup array since we're using the default comma exact match comma first to last this construction right here we'll always get the first units for whatever that product is we could do spill and that's how we did it and old person's right exact match when there are duplicates always got just the first one let's look at example nine what does lookup left mean well if we're looking up the range of a boomerang and we need to find a match in this column and then jump to the left to go and get the name of the product that has that range well vlookup could not have the match column in the middle of the table it could only have it if it was the first column in earlier versions it was no problem we switched over to index and match hey we're trying to get something from there and then match finds the relative position for index and together they can look up to the left well X lookup has no problem looking up that 22 in a lookup array in the middle of the table because guess what this return array we can put it wherever we want if it's to the left or to the right or down below it'll work fine now the caveat is you have to look at lookup array and since it's looking vertically through one two three four different cells that means return array has to have four rows just like the lookup array but for lookup left X lookup can do it now let's go look at example 10 now X lookup can do horizontal lookup vertical lookup and even sometimes both how did we use to do vertical lookup well we all know V is for vertical now when we use X lookup as long as there's the same number of vertical cells in both ranges vertical lookup is easy we used to do horizontal lookup with of course H is for horizontal H look up X lookup has no problem as long as there's one two three four columns in the first and the second X lookup can do horizontal now once in a while we have to do both it's rare before X lookup index and match had no problem index have the items to return and look at that they're vertical and match had no problem looking through a horizontal range because match would just count and find the relative position to and pull out the correct price well X lookup can't really do that so if you want to do it and the first range is horizontal you're gonna have to transpose vertical range to a horizontal now let's look at two-way lookup example 11 this is a typical two way lookup i'm looking up income and i need to look up exemptions this is a two way lookup tax table i need to get that seven percent and bring it back one of the old ways for doing two-way lookup of course was index with match match now we get to learn something really cool about x lookup if i start by looking up just the exemptions i look through this range and give it a table that has the same number of columns but multiple rows it'll deliver the entire column this is how you would look up a column so we're gonna look up exemption comma and for lookup array we're counting there's five columns comma so returned array we know it has to have five columns but it can have as many rows as we want now comma match mode we're definitely doing -1 because in a tax situation we could have more and we want it to pick up anything equal to whatever it might be out here or the next smaller item so minus 1 close parenthesis now when i hit enter it spills the array it looked up the whole column now that we have five rows in one column and i'm gonna cheat them and a copy this control c escape equals excel tab we're looking up income comma five rows one column that's the lookup array comma return array control v and i have to make sure comma and do match mode minus 1 close parenthesis now I want to count 1 2 3 4 5 6 7 arguments but it works 1500 no problem both of these work now we already saw one example where an old function in some situations was going to be more efficient to create it's the same with two-way lookup if I have a tax table and tax tables like this are almost always sorted consistently I'm using vlookup to the income , get the entire table , and four column index number we'll use two functions just like X look up X look up but now I'll look up exemptions within this range right here and because match and vlookup by default do approximate match that's it one two three four arguments close close and enter so X lookup and some other options can do two way lookup now notice X lookup looked up a column so let's go look at example 12 if our goal is to look up a product and then add values well how do we use to do it we would use index match would get the row number and we would either leave column number empty or put a zero and that would say get all the columns well there's an easier way with X lookup we're looking up quad comma within four rows one column and as long as the table has four rows we can put as many columns as we want when I close parentheses it will spill to the right if I move this down there I really want to add control Z so I put it inside of sum and there we are looking up a row enter example 13 if our goal is to look up a particular month and then get the whole column look up array has three columns return array has those three columns but when we look it up it'll give us the correct column and some will add if we go to our next sheet well we just saw that X lookup can return multiple items but this is a common task when we need to retrieve the whole record now before X look up and before filter if we really wanted the whole record then we could use columns inside of column index number and then copy it to the side we could use vlookup with an array constant we also could use filter to simply filter to show only a particular record in that data set that does involve knowing how to use that operator right there so it sort of seems like X lookup I'm looking this up comma within one two three four rows returned array and watch this I'm gonna highlight the same number of rows but four columns three to six three to six that's the important part however many columns we have is how many values that X lookup will spill close parentheses and enter now these methods return exactly the columns in the particular order they're in if we want something more dynamic on our next sheet now our goal is to return the entire record but not in the same orders up in the lookup table I don't want price in the fourth position I want it in the second position older methods well of course we could use vlookup and then for column index number we just look up the correct field name for our column index number now just for fun we could use vlookup and match and do a function argument array operation on all of the columns inside of match that would spill the correct record in the correct order here's how we do it with X lookup we first lookup product within those four rows and we give it the entire table this as we saw a moment ago when I hit enter will look up the record but it's in the wrong order now we need to rearrange the columns f2 we use a second Excel lookup but guess what for lookup value we're gonna give it all four columns in the correct order for lookup values means that X lookup comma when we give it lookup array will get the correct for relative positions comma and the combination of lookup value and lookup array delivering correct relative positions or column numbers will rearrange that horizontal range and when I hit enter it spills in the correct order now if we wanted to go old school and use index guess what these are two row number column number exact match lookups so instead of using match guess what we're gonna use the new X match lookup value I need to get the row number comma so I'm looking up the product and the last two arguments inside of X match comma match mode comma search mode exactly the same as X lookup and guess what the default is exact match so that's all we have to put comma X match and here we're gonna get tricky since we're trying to get the correct relative positions lookup value we highlight all four comma within that range we're doing exact match nothing else is required close close and that will spill now here's probably my favorite method and here's another example where I think I might be using vlookup I want to look up this comma within that table and I need column index X match comes to the rescue I'm doing a function argument array operation I put four items match will deliver the correct for relative positions for vlookup as long as I give it that range close parenthesis and column index number 5 f9 1 4 3 2 that is just too cool control Z now I do need to remember comma since approximate matches the default I need to put 0 to tell vlookup to do exact match but when I hit enter it spills so there's a bunch of different ways we can retrieve a record while ordering the columns the way we want now example 22 what if we wanted to just simply rearrange the columns look up the field names within the field names and I gave it the whole table so when I hit ctrl enter it spills the column and then I can copy it to the side now there's a few other examples if you want to download and check those out now the next 5 examples 23 to 27 we're not going to do them but I do want to quickly show you some important tips if our goal is to look up February to November and then add well we used to use to index functions in the colon would force the lookup functions to look up cell references well no problem we can do that with X lookup this X lookup is looking up the cell reference for February that one's looking up November if I hit f9 it doesn't show me the cell references but I can clearly see that I'm getting exactly what I want example 24 looking up a picture with a combination of defined names and a picture that's the old formula we used to use well guess what X lookup works just as well example 2500 now when you have multiple tables I have a video here with much better methods than X lookup we could use switch if it's choose and even the indirect function now the advantage to using anything as seen in this video for looking up tables is that we only have three tables to look up the problem as I see it with X lookup is we have two arrays so if we have three tables we have to do one lookup for the first column and then a second look up for whatever subsequent column there is example 26 no problem earlier functions could use wildcards asterisks means zero or more characters question mark means a single character so if I want to look up Cola but find Coca Cola I simply join wildcard on either side and it will look it up the only difference here when using X lookup while we have our two arrays the fourth argument , we have to put a two for wildcard to close parentheses and then we can use wildcards now the last example example 27 if our goal was to do a complicated array calculation as part of our lookup well a lot of the times we use the lookup function sometimes we used index and aggregate but guess what I'm using that same array formula right inside lookup array annex lookup can do it too alright that was an epic video with 27 examples of excels ultimate lookup function X lookup now if you like what you see in this video click that subscribe button because guess what there will be lots more videos including more X lookup we'll see you next video [Music]

Channel: ExcelIsFun

Views: 122,958

Keywords: Excel, Highline College, Mike Girvin, excelisfun, EMT, EMT 1600, Excel Magic Trick 1600, XLOOKUP, New XLOOKUP, Office 365, Office 365 XLOOKUP, The New XLOOKUP: Compared to VLOOKUP & INDEX, The New XLOOKUP Function for Excel, XLOOKUP in Excel is VLOOKUP Slayer, VLOOKUP... Gone But Not Forgotten, XLOOKUP or INDEX-MATCH-MATCH Head-to-Head, Microsoft introduces XLOOKUP in Excel – and it's a big deal

Id: WO6Kjba5EPc

Length: 25min 2sec (1502 seconds)

Published: Fri Sep 06 2019