XLOOKUP Function - The Successor to VLOOKUP and Its Much Better | Alan Murray | Skillshare

XLOOKUP Function - The Successor to VLOOKUP and Its Much Better

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
9 Lessons (35m)
    • 1. Introduction

      2:35
    • 2. Using the XLOOKUP Function - The VLOOKUP Successor

      5:29
    • 3. Performing a Range Lookup with XLOOKUP

      4:30
    • 4. Finding the Last match and Using Wildcards

      4:00
    • 5. Looking Along a Row - HLOOKUP Replacement

      1:51
    • 6. Lookup using Multiple Criteria

      2:20
    • 7. Creating a Two Way Lookup

      3:54
    • 8. Using XLOOKUP with other Functions

      4:17
    • 9. Creating Dependent Drop Down Lists

      5:58

About This Class

In this class, you will learn the new XLOOKUP function of Excel. This awesome new function is the successor to VLOOKUP and HLOOKUP (you can now forget about those functions).

VLOOKUP is the third most commonly used function of Excel (statistics from Microsoft), but it was heavily limited despite how handy it was.

XLOOKUP changes all this. The previous limitations have been fixed and there are also additional features that this function brings.

This class will show you how to use XLOOKUP, why its better than VLOOKUP and also show some advanced uses of the function.

Note: XLOOKUP is currently available only to Office Insiders and will be available publicly very soon. You can sign up to be an Office Insider here.

Download the practise file to follow along with the videos and the project file for the exercises afterwards.

Any questions, please post them and I will get back to you.

I will see you in the class.

Transcripts

1. Introduction: Hello and welcome to this class on the new X look up function Off Excel. My name is Alan Murray and I will be your instructor now The X look up function was released at the end off August 2019 and it is only available at the moment at the time of doing this class to office insiders. But a time he watch this. That may already be different, but the moment it's only available to those who are in office insider and anybody can sign up to being insider through your office. Free 65 account on this will very soon be rolled out. Everybody who uses Excel. Now what are we going to talk about in this class? Well, we're going to start with How do you use this new amazing function? And then look at how it differs to look up formulas from the past. You know, these formulas are still around. They haven't gone anywhere. But X look up to an extent, is here to replace them, especially in the case off Villa Cup and hates look up. So how does it differ? Well, our its benefits and talking about benefits we're going to see some of the extra features. So there are some benefits through little design changes and ways of working. The X look up can do, and we'll see that in the first a lesson off this close. But then we'll also start looking at some of the extra things that X look up. Does that v low carbon match in index maybe can't do well, Then start looking at some or advanced ways of using X look up. We'll see it being used with multiple criteria and how much easier that is then, right in some product functions or crying help of columns for Villa Cup, We'll see how it could be used with other functions of excel and also with other features off Excel on the demonstration. There will be how X look up can help us create dependent drop down lists. So we've got lots of fun from this amazing New Excel function to get to grips with. So let's get started 2. Using the XLOOKUP Function - The VLOOKUP Successor: Hello and welcome to this first lesson where we will look at using the X look up function in a classic look up formula scenario. So for those of you who have used functions like V, look up, hate, look up or the index match combination before you've probably used it for this kind of scenario. And that is to look for a unique I. D. And to bring information about it from a different column typically used to link sheets together, consolidate data from different sheets or two, maybe compared them. Now, in this example of gun ID'ING column I and we want to retrieve the department off the I d. And in this example, with the lists of right next to each of Iran, there reasonably small. Where's for yourself? There may well be on different sheets, even different workbooks, but we're going to put out X look up functioning Selby to we're going to see how to do it on, especially for those who have used at functions like veal cut before. How does this one differ now as we begin and I open up my ex look up function initially may look a little bit more complex than something like the look up because actually got one extra argument or question. There were five arguments here. Look up value, look up array. Return a right match mode and search moat, but only the thirst free. A mandatory on a key thing here, for this example is X look up defaults to the exact match V. Look up de folks to the approximate match, so you normally have to write false or zero for the last argument there. Here, we won't need to weaken. Simply provide to look up value, which is the idea, and sell A to putting a comma. Select my look up array, which in this example are the ideas in column F. So a key difference here to V. Look up, they probably for the table array, and you select a bunch of columns ensuring the 1st 1 is the ones looking here. We can just select that one range of values, and when we put the comma, they prompt for a return array, not a column index number like some of you may be used to. This is so much easier. We can just select the range of departments, and Aziz is that if I close my bracket and press enter, it will work. Now, before I run this, I should look at fix in my to arranges for the look up array in the return array. I'll use my F four key on the keyboard to do that. Mr. Let that at four. And I know run this function and here we have a i t for I d t a free zero to. And if I copy that down, I have the department for the other free ideas as well. On that X look up is actually a little bit shorter than what your V look up for. Murder would be like and also a lot easier, especially for people who are new to this function. So much more straight forward. What are you looking for? Where you're looking for that. And where's the right you're returning from? No, In the next example, I have a prompt for a name for it. Now for those of USVI, look up before you will know that V lockup cannot look to its left. And look at this. The name is on the left of the day. But this is not a problem. for X Look up because we give it a return. A right, not a column. Index number. So X Look up. Look up! A raise. The i d comma the look up array. See, we can just select the array. We do have to give it a whole table. We haven't got a worry about this left right anymore. Let me fix that With F four comma. The name is simply the range of names going through this one a little bit quicker because the same thing, really just a return arrays to the left off the i. D on. I wanted to demonstrate this for those who have some experience of the look up because it is a constraint of that function. If I run this, we're now retrieving the name Fern as well. So I have X lookups returning the department and the name from those ideas. Just one more thing I want to mention in this lesson is that because we do not have to give it a column index number because it runs off these, look up a raise and return a raise. It also makes more durable because look at this. What if I insert a column in between e and F. No worries. We'll if I insert one in between GN Hey, h, No worries. No. Ever off. My ex lookups are broken. Even note is now a column separating the name and the I d on even the idea in that department Where is that? Would have broken a V look up unless you'd used other functions toe make it more powerful. But V look up alone cannot handle this in a way to explore cup can. 3. Performing a Range Lookup with XLOOKUP: hello and welcome to this lesson where we will look at using the X look up function for a range. Look up. So we've seen it for the exact match, that precise match. Now we're looking for the approximate match, which is normally referred to as a range look up. And what we have here is in my days of sows and some totals on, we want to provide a discount. But that is dependent on how much they spend now. Another difference in this example is that the look up table on the right hand side is actually in a table. So ever look up in my table designed tab, I can see this table to refer to his discounts, and we don't really need to know that. Do X look up, but you will see the references in the formula change. Now I want to be clear at this point that although uses of tables are encouraged a lot in excel, it is no, it doesn't matter is not necessary whether we have a table or a range of sales or a named range. Or however you refer to these arrays. Index, Look up. It makes no difference I just want a little bit of exposure to a table being used in this class. So in cell C to its X look up time, look up value. Well, that is the amount they Spence, which is still be too comma. Look up array. Well, that's the array where they spent it, some going to select E two down to E seven. And there we have the reference to the Spence column of discounts that is the table doing that. I could have written that if this tables on a different sheet, and that's where the advantages of using tables in this scenario would be amplified. Right now, it's just a tiny array of sales next door, so the benefits are not too clear, although it does look a little bit nicer. Comma Return Array is the array off discounts F two to F seven or simply known as the discount table during Discount column off the discounts table. Now we do have two optional arguments on the end. In the previous lesson, we didn't have to worry about this because it defaults to exact match, which is great because 98% of the time, that's what you want. Right now, we don't so comma brings us into match mode. And after the option for exact match, we have next smaller, then next larger and also the option for world card, which will demonstrate in the later lesson. Right now, we were next. Smaller item minus one. Or come just double click on that option. It puts minus one in for me. Close off the bracket. Don't need that search mode question right next, Right now. Lesson to come on that soon For presenter, 12% is what that amount would give you, and if I copy it down, then we hope for the others. So 188 would bring you a 2% discount. Where was getting is a 5 to 4 would give you a 5% discount. Now something you may have noticed, especially if you have used functions like V look up before, is that in column E. The first column that spent column It's not in order. And when you do range lookups with V, look up. The first column must be in order here. It's not, but it does not affect the formula. How awesome is that now? Yes, I probably would want that in order. If I'm a user here, it makes most sense. Why zero in the middle on 500 at the bottom? So maybe old Click on one of those sales and all pop to data, and I'll sort those on that column at that table by that column, Formulas unaffected. It didn't matter. Here's an order or not, but for my own peace of mind, inner balance. I'm going to put that into warder cause ideally, would be like that. I just want to demonstrate that the formula is not affected if for some reason, the order is so There we go, X, look up for a range. Look up. 4. Finding the Last match and Using Wildcards: hello and welcome to this lesson where we are going to look at two unique differences with X. Look up now. The plan here is that we want to return the last mumps sows for the sows person in cell E to which is D height. The initials off somebody, but you're noticing column I that we don't just have D. H or a F. We have a reference which seems to change every month. Order the South person's initials of always that sounds person. That's how we identify them and noticed that the table on the left is in order by months, something that where a Shuman is always the case. So the last month sounds will be the last one you see in the list. So let's look or ex look up doing this cell F two. I'll begin the X look up function and the look up value Well, that is sell E, too, but not just 82. A need to put in an an percent and then in double quotes an asterisk score star. This is your wildcard character. We need a wild card search. I want to combine the value from A to company D High H, but it could be lk your a f and something else. I don't know what the reference number is going to be, and I don't need to. Now when I put my comma look up, array will be the array of references back to using a sheet here. So it's a two to a 16 comma. The return array is going to be what I want. The cells figure. Let's let the cells C two to C 16 comma and now for the match moat. In a previous lesson, we saw us using next small item. This example. Wildcard character match. We need to specify this with X. Look up on the description. It tells me we can use the world card characters off the asterisk, the question mark or the tilde. Now I'm going to double click to say we are performing a wildcard match. Another comma brings us on to search mode. Now this is important. Normally, your search in first to last 99% of the time and you're probably only finding one match in the array. But this time there's no there's multiple matches multiple d high. H is on. We want last to first minus one. I'm going to double click on that to perform a reverse search. It's gonna look from the bottom up the list, something that functions like V Look up on Match cannot do. So I closed my bracket. Now on presente 6 10 is the last match for D. H in that list. And if I click in cell E two on maybe type L. K. The last match for L. K is 16 to 4. So that is brilliant. X look up can perform wild card searches. It is not an exact string that you can match, and they can also do a reverse searching A list Brilliant to find the last item or Arvin in the first. 5. Looking Along a Row - HLOOKUP Replacement: Hello and welcome to this lesson where we will see the X look up function searching along a row instead of down a column like we normally do. Your data is normal. East orders lists on. We're searching down the column for that ideal for that value. But in Excel before exe, look up was around. We had a function called high H Look up to do this V look of a vertical height Look up for horizontal with the introduction now off X look up. No need for two functions This one conduce them both. In this example, I have some names in Row four and some sowles values in Row five that I want to retrieve. So I e to, um company looking for Helen. So be to its X look up time. Let's run our X look up the value were searching for the value of Helen or whatever. Other value. Maybe in so a to comma look up array, we just simply select the row instead of the column is an array. We write it no different comma. The return array will be the values. So look along that array off at names and return finished array of values. Remember it defaults to exact match. And that's what we're doing. I'm looking for an exact match on the name of heaven. Close bracket press sent up to zero free. But if it did go and change the name to maybe Phillip, then I've got 22 for Philip on that is X Look up looking along a road. 6. Lookup using Multiple Criteria: hello and welcome to this lesson where we will step our X look up, functions up a notch, and let's look at how we can use it to search for values from multiple columns or criteria . Now these could be whatever criteria as many columns as you want. But the scenario I've got here is I need to match the first name and the last name, both for those need to be a match and then return a salary from column high H so in cell C two, let's start the X look up function. Andi, look up value. Now we've got to look up values with two columns. So what I will do is select the value in a two. As the first criteria has to be, Miguel put in an n percent and joining the last name Selby too. Now, both for those together putting the comma look up array, We're going to do the same thing. But to the arrays now. You couldn't do it exactly like this With the V, Look up function normally had to create a help a column or you have to write on array formula. This is so much easier. I simply select the array for the first names point in my M percent. Select the array off the last names Coma return awry the salaries nothing more needed defaults to exact match. Don't need any match mode or search mode. Questions. Close bracket on When a presenter for Miguel here 30 Free 758 And if I change, those names won't worry about for now. You contest that that is going to return another celery. So here we were able to test two columns could've been free or four columns. But it's much age of this X look up function, then, how it has bean in the past. 7. Creating a Two Way Lookup: hello and welcome to this lesson where we will look at using X, look up for a too wide look up, something that people typically used the index match combination for. Let's look at how x look up and do this now, to be precise, we're going to use to X look up functions one for the row on one for the column. Now what we're looking for here is I'm looking for leads says that in Selby one and fruit says in So a to let's start off with elites. All right, the X look up function in Selby too. And look at values leads or be one comma. Look up. A ray will be the array off places going for these a little bit quick. And l because we've got a few ex lookups under a belt. Don't hang around too much comma. The return array. Now this is where it gets a little bit different. I'm going to select all of these values. So all four columns, but also all of the rows as well. So one x look up to search for the place. A long road one. The one touch one when it finds leads return the corresponding values from that column. Self selected order vote. If I close bracket and run this function on its own, it returns all off the values for Leeds. So all five values. But I could be hundreds or thousands, depending on your spreadsheet. These are just small spreadsheets, toe keeping simple, and we'll see how a function like that can be used. We've other functions in the next lesson, but that is interesting, and this is because it's a dynamic arrays now and excel that dynamic array engine behind it is able to pull multiple values back, which in the past we could really use V look up, fall until this engine was out. That's awesome. But what we want is the value for fruit. We don't want all of them. I want the fruit value or whatever else might be typed in a to. So let me go back to that first to be look up notice at the moment. If you're new to these dynamic arrays that in Selby free the formula in the formula bite grayed out, I can't change the formula from here. The formula it doesn't actually exist in there exists in B two, but it's spilled to those cells. They double clicking Selby, too, and I'm going to write another ex look up before that one so that we can get it to look for the value in a to in the list of products. Unfortunately, the formula has got in the way a little bit here, so gonna drag behind the formula, but t d to two d six There, comma, the return array is the X look up look for fruit in column D and returned the value from that list of values that X look up has returned, so I could just put my clothes back in the end. Default to exact match Press. Enter 4 13 for Fruit and Lietz. But if I change this to T T and Leeds, it's 106 Whether change leads have got a drop down here to to Manchester, Manchester and tease 39 free. So here we're looking along a row and down a column by using two X lookups, one inside the other 8. Using XLOOKUP with other Functions: hello and welcome to this lesson where we will see X look up used with other functions. So following on from the previous lesson where we were able to do a two way look up and we used X Look up with another ex look up. Let's see it used revolvers because let's have a quick recap if in Selby too. I wrote an extra cup function and asked it to look for the value of Leeds Selby one in the array of places which in columns, f toe, I hear. And then for the return array. If I gave it all of the numbers from the area and I closed bracket and presidenta, it would return all off the values for leads or if I changed leads Toa Plymouth. It returns a lot off the values from Plymouth and then we used in another ex look up before , but we can put any over functioning. So, for example, if a went in to Selby, too, before exe look up, I could put the sum function or an average function or any other function that might prompt you for a range of values and do something with them. I'm going for some here, Close bracket on the end on what I've done is summed those values so essentially created a sum, if if you will, but using X look up, I'm some in the values from Plymouth for a moment, but ever changed. Play Murph to Lincoln. I've got the values from Lincoln, so that's pretty awesome. That X look up doesn't just return one value it can do. It can return one value, but it can also return an array of values. Return arrange so any of a function that may require a range to do something with it. We can use X look up with that on That opens up a lot of possibilities. A lot of potential. Now that's with a normal some. But I've got coffee in Cell A two. I've got a product name as well, so if you go back into Selby too, maybe I only want to some the values that first certain products like coffee because I've got these mumps. I've got free coffee values in there. So before the some function or sorry instead of some function before the X look up function , let me just delete some there. I'm going to put I some if open bracket. So what range of my testing? I'm testing the range of products. I'm looking for coffee at the moment. That's D to two d 16. Unfortunate former just getting in the way but slipped behind it. Comma criteria. Whatever values in cell A to comma and then arrange to some is X Look up. It's returning that range once again. This could be an average of four or accounted for any other function. It requires ranges. Here. I'm using it. Look up to get the some range or close off that bracket and press enter on day. Other values just for the coffee cells. A Lincoln. But I can change Lincoln to Leeds and Albert leads for coffee change coffee toe fruits and I got fruit in Leeds. So got to criteria is going on here. Ones commoner of crossing a row Ones coming down a column and it's bean sound. So this extremely versatile conditional. Some function simply by using X Look up. We've other functions, like some and some if lots of possibilities about how the factor X look at returns. A range can be used at first, um, awesome dynamic formulas 9. Creating Dependent Drop Down Lists: hello and welcome to this lesson where we used the X look up function to create dependent drop down lists and fresh from the previous lesson where we saw X look up being used with other Excel functions. We can also use it with Excel features. The fact that X look up can return a range can be used with various commands a eager in Excel on. I want to use it in this lesson with the data validation tool to help us create effective drop downs. Now I've got a type column in a beverage. Got free types of beverage, juice, coffee, tea and each have their own drop down. I want a drop down list for the type juice, coffee, tea on, and that will determine what my list of beverages will be now before we get going with X, look up, I'm going to create two named Ranges. I'm going to select sells E 12 g one, the different types off beverage on in the name box in the top left. I'm simply going to call them type. That's what I call it. Or could it type? Press an end to there and then I'll select the range off beverage options, which for me is Etude and two g six creaking that name box on down to call that one drinks . So I've got a name range for the type in a name range for the drinks and just select themselves a to to a five. Here. I'm going to put a drop down list in those four cells so pop into the day to tab data Validation Button. I'll choose list, and for this source I will simply type equals type. I'm referring to the named range with the free, different drink types. Click OK and I have a drop down. I can choose coffee. I can choose juice. I can choose coffee again in these four cells that are set up that data validation list. So we have really, quite quickly because we're here for X. Look up on. We're about to do that now, but I just wanted to briefly show that set up. Hopefully a lot of you watching this would have done that kind of stuff before. Now let me just get rid a couple of those in column B. If I said excels. B two b five. I want to drop down and here is dependent on the previous selection. Now, before our crate. That list, let's check out X Look up equals X look up. And really, what we're going to do here is no really any different to what we've done in previous lessons. The difference is that I'm going to use this within the data validation role within Excel feature, because with the X look at function, the value of looking for he's coffee, the waiver value was chosen. Sell a to coma. The look up a right, Is it type named Redford type? It's the different options have along the top comma. The return array is drinks. It's the nine drench for drinks. Can you return? What? What color matches? This is just like when we looked for the place like Manchester or Plymouth before returning Column doing the same thing. What we have named ranges here. So I've seen X look up work ranges of cells, seen it on the table, were now senator of named Ranges. Doesn't matter how you reference as long as it it works for you. It was just closed. Break your neck and press enter that returns all off the values for that type. So for coffee, But ever change coffee to juice, I'll get the juice items of a change tear. Get a T items. I get this zero in a moment because they're smaller lists but change its coffee than about two coffee now is I don't want that. I want to drop down. So let me double clicking Selby too. And I'm going to take a copy off the extra cut function. Let me control. See to copy. And I would just escape and ah, delete that. Former No, I don't need any more. Still excels B two to B five that swear on the drop down list data tab, data validation going to choose a list and the source I will paste in control V my ex look up function. So when I click OK, I now have a drop down list here, too. And now I've got a drop down off the coffee options. And if in the next one I chose from T, I get a drop down off the tee options in the column next to it, this drop downs dependent on the 1st 1 So it does not work in the third column Unless something is chosen, they're like coffee again and I'll get a coffee list. So we have created a drop down list, which is dependent on what is selected from a novel. Drop down a Dependent drop down and we've created this with the X look up function once again, like the previous lesson. Lots of potential here. I'm using X Look up in a data validation rule, but there are some quite clever ways we can probably use it with a directional functionality that you might use. The factor. Eggs or cut can return a range to a formula or to an Excel function. You said a lot off potential, too. Do some very fun things.