 Hello and welcome to another D Brown Consulting Excel and Power BI Webinar Series. This is always on the third Thursday of every month from 9 a.m. to 10 a.m. Central African Time and it's sponsored by D Brown Consulting. So today we're going to talk about XLOOKUP. XLOOKUP and how it retires VLOOKUP. And I'm also going to give you a methodology for extracting any data from any table in Excel. Very efficiently, very cool methodology that uses dynamic arrays, uses the spill, you know the spill trick for dynamic arrays. And basically the modern Excel, how you should use Excel in a modern way. So this is going to be fun. We're welcome and let's push on. So also please join us in our Power BI user groups, Power BI user groups live lab, which we have every third Saturday of the month from 9 a.m. to 12 noon. That's the third Saturday of every month. You check out www.pbiusergroup.com. So check that out and join us. We have every third Saturday of the month. Talk about Power BI for three good hours. Bring your laptops with Power BI desktop installed. If you have Excel as well, you can bring it. You could use Power BI on Excel, which is really Power Pivot, Power Query, DAX and M. You can also move along and follow along with us. So join us. Any one of those thoughts are Saturdays. We're lucky to be sponsored by Lagos Business School. So that's our latest sponsors. So you could join us there and we'll have fun. So I'm David. I'm David Brown. I'm the managing partner of D Brown Consulting, also an international consultant to the World Bank. I'm a Microsoft MVP on data analytics, and I have over 20 years working with Excel, data, reporting, accounting, finance, and I'm also a master trainer. So I do a lot of training. We also check out officetraininghub.com. If you check that out, you see some of our online courses there as well. Also a chartered accountant and have some other certifications. So that's about me, David Brown. You can follow me on Twitter at D Brown Analyst. That's my Twitter handle. And I'll see you and continue the conversation online. So what are we doing today? We're talking about XLOOKUP. So let's just jump straight into the demo. So XLOOKUP. So I'll just kind of remind you of what VLOOKUP used to be. So VLOOKUP. We have an exact match. We're trying to get an exact match. So here I want DANGOTE's eBit. So this is a table, a typical table, and I want to extract DANGOTE here, the eBit, which is this value. So in our typical VLOOKUP, we just do equals to VLOOKUP with tab. And VLOOKUP says, hey, what are you looking for? First, the lookup value. That's DANGOTE. I put a comma. Then table. The table we are looking up. So the entire table. Yeah, looking up the entire table. Put another comma. Then what column are you looking up? Well, the column I'm looking up is the column that has eBit. So that's where is eBit. eBit is here. So that's one, two, three, four. So usually we type four. That's bad practice, I know. But anyway, let's move on. And then is it an approximate match? You see the default of VLOOKUP is approximate match. Well, I really need an exact match. So that's one of the differences you're going to see. And then I close my bracket and I enter and I get to my 108. So how do you do with index? Index, if you type index, index and I tab, you see index has two versions. Index has array, row number, column number. Then you have reference, row number, column number, area number. We're using the first version of array of index. So the first thing the index says is, hey, show me the entire table you want to work with. So this is the entire table. Then the next thing it asks for is what is the row number of what you're looking for? Row number. How do I get that? Well, if you see done, go te. Let's count. You have one, two, three, four, five, six, seven, eight. Eight, eight. So that's eight. So obviously typing eight and counting doesn't really make sense, right? That's where a match comes in. Use a match to do that. But let's, let's, let's move along. So, so dango is on the eighth row. What about the columns? I put a comma. It says, what is the column number? So that says EBIT, which column is EBIT? One, two, three, four. So my table has five columns and EBIT is number four. So I put four, I close my bracket and I enter. And yep, I got it with index. What about XLOOKUP? So XLOOKUP. Let's have a look at XLOOKUP. So XLOOKUP, look at the syntax here. The syntax is lookupValue, lookupArray, returnArray, matchMode, searchMode. So that's XLOOKUP. LookupValue, lookupArray, returnArray, matchMode, searchMode. Let me give you a shortcut to understand how to use XLOOKUP. XLOOKUP is what are you looking for? Where exactly are you looking for it? So what are you looking for is lookupValue. Where are you looking for it is lookupArray. So which was the column that you're looking for this thing from, right? Then returnArray is okay. So which column contains what you want to return, right? And then matchMode is okay. Are you looking for an exact match or not or what exactly are you looking for? And searchMode, that one is special. We're going to see that later, right? So lookupValue, this dangote, right? Put a comma. Where am I looking for it? Well, I'm looking for it in this first column here. So I can highlight the entire column. Let's say this, yeah. So that's where I'm looking for it, right? Now, of course, if it was XLOOKUP, it would be horizontal. And XLOOKUP works whether H or vertical or horizontal. It works fine. So it replaces XLOOKUP as well. So this is the lookupArray, yeah? Then if I put a comma, what's the returnArray? What are you trying to return? I'm trying to return something in EBIT, the EBIT. So I'm going to highlight the EBIT column. And that's it. And then if I put a comma, you see match type. Exact match is the default match. So unlike VLOOKUP, exact match wasn't the default type. In XLOOKUP, exact match is the default type. If I put a comma, these are the world cards. And not world cards, really. These are the search. This is the search criteria, which is, here you have search first, last. We'll see that very soon. So really, I don't need, since the default is exact match, I really don't need to specify anything. I can just stop right there and enter. And I get my value. So that's a simple way of using XLOOKUP. Let's start the exact match way. Let's look at approximate match. Let's say approximate match. How would you use VLOOKUP to get approximate match? So let's look at that now. So approximate match, you have sales amount over 17,500. And I have a sales table here. So this is my sales table. And if this is my sales table, we have, we need to, anybody that sells between 0 and 10,000 has a rating of 0%. Well, you sold between 0 and 10,000. We should try harder. No bonus, no vacation for you. You sell between 10,000 and 15,000. You get a 1% commission. And you can go on vacation in Abuja. You have 15,000 and 25,000. One and a half percent and oil. So what is 17,500? 15,500 is somewhere in here. So 75 is in between this somehow. Then you get a 1.5. So if you want to do a range lookup in, whether VLOOKUP or XLOOKUP, if you want to do a range lookup, you need to modify your table a little bit. You need to put the lower end of your range here. So I'm going to put the lower end of my range, thousand and one and 15,000 and one. And the lower end. So I'm just typing the lower end of my range here. So this is 25,000 and one. 40,000 and one. And then 60,000 and one. So those are the lower ends of my range. Then I can do my VLOOKUP. So the normal VLOOKUP, let's see VLOOKUP tab. So lookup value. Again, this is the amount of lookup value. Then we have a comma. Then your table array. Table array, what's the table array? Well, this is the table I'm looking up from, this table. Then I put a comma. Then the next thing is column index num. What index was the column number you're trying to pull out from? So this first, second, third. So it's the third column. Three. Then if you look at the comma, the next thing is range lookup. And the range lookup has two options. Approximate match is the default. So since approximate match is the defaults, and that's what I need an approximate match, I don't need to put anything. I just close my bracket, enter, and I get 1.5. So this is the old way. This is the lookup, the old way of doing it. So the new way, if I do equals to x lookup, x lookup, x lookup again, lookup value, comma. What is the lookup array? Now the lookup value, this 17.5, we're looking for it in this single array. This array, right? Comma, what is the return array? Well, this is what I want to return. So I just highlight. Now if I put a comma, you'll see that exact match is the default. But right now I don't want an exact match. What I want is the next smaller item, or is it the next larger item, or is it just a wild card? I'm looking for a wild card character, right? So you see, I have more options, not just exact match and approximate match. The approximate match we're seeing, are we moving from smaller to higher? So is it exact match next smaller item? So obviously 17.5 is not here. The next smallest item to 17.5 is 15,001. Which is what VLOOKUP does, but this is such a clear definition, right? So that's really what I want. Do I have to specify that? Now I don't need to specify search mode, or I can leave that out, right? So I close my bracket and that is how I use x lookup. Now one big difference between x lookup and doing approximate match is that VLOOKUP does something called binary matching. Binary matching, which means it needs to go one at a time and this list must be sorted from ascending to descending, or sorted ascending, right? So it needs to be sorted. So if I mess up this list sorting, x lookup will break. So let's test that. I'm going to sort by this column. Let's see data up here, A to Z. Guess what? Look at x lookup. Now it's getting the wrong percentage, but that's VLOOKUP, sorry. VLOOKUP is getting the wrong percentage, but x lookup hasn't budged, but x lookup is getting the correct percentage. Whether your list is sorted or not, x lookup will find the correct approximate match. That's cool. That's just super, right? So that's x lookup with approximate match, far better than VLOOKUP, for sure. Let's look at last match. So what if I want to get a last match? So I want to find what do I mean by last match? Well, if you look at this, I have some months, I have some products, and then I have the sales for those products of that month. And typically when you grow tables, it's usually the very last data, maybe that has what you're looking for. So here I have data and you can see, okay, this June is the last data and I'm looking for actually I want the last sales for Milo. So I have 554. 554 is the last sales for Milo. What was the first sales? First sales is 268. When it comes to lookup, you usually just get only the first item. So I may use the old way to get the first sale. The first sale for Milo is 268. So I use my typical VLOOKUP. We say VLOOKUP. What is our lookup value? We're looking for Milo, comma. Where are we looking for it? Well, it's this table here, right? So I have to highlight. I can't highlight from here. I have to highlight from here. Yeah, because the first column must contain what you're looking for for VLOOKUP to work. So if I highlight this table all the way up there, yeah, and then I do a comma. And what I want to return is the second column and comma zero because I need an exact match, right? So I close my bracket. Now zero or false, right? Zero or false is the same thing. Zero and false actually work. Same thing, right? Zero and false. Right. So when I enter, it finds 268. And what I need to do since I'm going to drag this down to the right, I'm going to drag it down. I need to lock. So I'm going to lock this up. So a small trick is to click on this to highlight. I need four to lock. I'm locking that up. Yeah, because I need to drag all the way down. Right. So last sale. What I want, I don't want the first sale. I want the last sale of Milo. You can't really do that with VLOOKUP. You just can't do that. So I need to pick this up. I need to pick this guy up. How do I pick that up? Really can't work. So let's use XLOOKUP. So XLOOKUP first sale. Let's find the first sale for XLOOKUP. So the first sale. Bring this up. So the first sale will be equal to XLOOKUP. Same as VLOOKUP. I'm looking for this guy, comma. Where am I looking for it? The lookup array. That means I'm going to look for it at this list from the top to bottom. Should lock. I'm going to lock it up. So I'm pressing F4 to lock it up. Top to bottom. Then I put a comma. Then the next thing is the return array. Return array that is from here all the way to the end. I lock that up as well. So I just pressed an F4 key. So I'm back up here. So this is the formula. This is my formula. Let's move this out of the way. So you can have a look. So this is the formula. I lock close my bracket. So this is XLOOKUP. Looking at this in a lookup array in the return array. When we enter, we get the same 268. Come to the end. We double click. And there we have it all the way down. XLOOKUP can do also. It can do a last lookup. Basically looking at the last match. So lookup again. XLOOKUP exactly the same thing. Just take this down a bit. So it's exactly the same thing. It's looking at that. Where is it looking for it? It's looking for it in this product list from top to bottom. Right. I can lock it as well. I'm going to press my F4. Put a comma. Then where is it looking? That is your lookup array. My return array is all the way here. From this all the way down to the end. That's my return array. That's what I want to return. F4 to lock again. If you look at the formula again, that's it. Then I close my bracket. So here we're looking up this. We're looking it up in this list. Which has all the values. And then I want to return this list or this array. Something from there. So if I enter. You get what it is. Which is first match, isn't it? But that's not what we want. We don't want first match. If you put a comma. You see the exact match. Are we looking for an exact match? Or we're trying to match the next smaller item or the next larger item. Well, we're looking for an exact match. So it's exact match. In fact, I can even ignore it. Since it's the default, I can ignore it. You can just put the next comma. Now this is where the magic is. This search first to last. First to last is usually the way we do it. I mean you're searching from the top to bottom, right? What about if I do last to first? If I do last to first and I close my bracket. What I've just told VLOOKUP, not VLOOKUP. XLOOKUP to do is find the last one. 554. If you look down here. You'll see that Milo is 554. Maggie is 666. And then Nescafe is 355. So if I come down here. And drag this down. So if I come here and drag this down, double click. You see it finds the last sale. So this is the first sale. This is the last sale. And then you can do kind of a growth formula and stuff like that. You can do that with VLOOKUP. That's not possible. Let me show you an even more modern way to do what I just did there. So I'm going to try and replicate this all just using three formulas. So regardless of how long this list. Let's say I just need a list, a product list. Now in your new Excel, your new Excel, which is an Office 365. You don't have Office 365. And you need to have updated the latest Office 365. You will have what we call dynamic arrays, which is really the new Excel calculation engine. So if I say equals to unique, if you don't have this function, that means you don't have this unique. You don't have dynamic arrays on your system. You haven't set it up. It doesn't update it. So unique. What does unique do? Unique is so cool. Unique is a new dynamic array. If I come and highlight all my products. And what I want is a unique list from those products. So look at it. I'm just highlighting all the products and closing my bracket. And then I just enter and guess what happens? It just spills a unique list for me. Just spills it. Excellent. Now I mean you could even go in. So this is formula in a cell. If I come here and type maybe something to obstruct, guess what happens? It says, hey, I can't spill my answer. Yeah, yeah, disturbing me. And I can't delete any one of these because really it's just a spill. Look, I'm trying to delete it. It can't go. The only thing I can do is delete the first one and then everything goes, right? I'm going to undo that. And then I could actually come into the beginning of this formula and say, hey, I'm going to sort you. I need this list sorted. So I'm going to do sort. I say sort, which is another dynamic array function. And I enter. And that's it. Sort. But the thing about these arrays is that every function is an array function. Every formula now in Excel is an array formula. So let me explain that with X lookup. So if I do an X lookup, right? And in the X lookup, I say, hey, what's the lookup value? This is the lookup value. But really when I don't just want to look this up, I want to look everything up and I want you to spill. So if you see lookup value and I want to look up all the values and allow it to spill, what do you do when you click on this? Because you know this was a dynamic arrays. You put a hash key at the end. Just a hash. Just put hash. Just look at that. So put in hash. See what it did? It just spilled, right? Kind of highlighted everything, knowing fully well that this is a dynamic array function or formula in there. So if I put a comma and the lookup array is just like the same thing. Lookup array. I'm going to highlight this guys. I like all the way down, right? You can lock it. Press F4. So that's my lookup array. Yeah. Then I put a comma and then my return array. Return array. Come in here. Lock this. Right? F4. Right? So that's F4. I lock it. Now if you look at the formula, everything, you're looking this up. In fact, this up I'm going to lock. I just want to lock the column so that I can take it to the right. I'm looking the column and I'm looking this up, which is everything here in this list of products and I'm returning the sales. So if I come to the end and I close my bracket, when I enter it's going to spill. It spills all the answers. Everything in one, right? It spills everything. So water. I have water there. You can see water is 534. Water is 534. So I can copy this formula, which is in a single cell and paste it here. It's going to spill the same thing. But the difference is if I come to the middle of the formula here, I delete this. I put a comma. Since, hey, do you want an exact match? I want the next smaller or the next larger item. Well, I want an exact match. So I'm just going to ignore that. That's the default. Then I don't want first to last. I want last to first. So I want you to look up last to first. So I'm going to put a tab. That's a minus one. I close my bracket and I enter. And now it's looking at the latest sales. This is the last sales. This is the first sale, last sale. Latest sales. So that's exactly like this one I had up here. But only difference is this is now sorted. So if I take this sort, I could copy. If I could copy this formula here, this sort formula, I could come in here and do a sort as well. If I click on this and delete this and I come here and say equals to sort the unique list. So sort the unique list of my products. I highlight all my products, close my bracket and enter. Look at that. So sort the unique list. And now it's sorted. Now you can see that exactly the same thing. The only difference is I didn't make this dynamic. So I kind of had to drag it down. But this one, the whole thing is written in three cells. If I delete these three cells, everything goes. If I delete that, everything goes. So this is modern. This is really modern. Dynamic arrays using dynamic formulas and functions X lookup all working wonderfully together. Right. So next, what are we going to do? The next thing and the last thing we're going to do is build a dynamic lookup table. We're going to build a dynamic lookup table, pull out a whole mini table from a big table from a big table of data. We're going to pull out a subset of this table automatically using X lookup. I'm going to use X lookup on some interesting tricks and techniques for it to automatically pull out a smaller table from a larger table. So we'll see that coming next. Great. So we're now at the last stage of our webinar where we talked about various aspects of how you use X lookup. But the last stage is we're going to build a dynamic lookup table. What I mean by dynamic lookup table is like a mini table looking up a major table. So usually when we have a big table of data, our reports are really a small version of that big table. So I want to give you a very cool dynamic way of building that out using X lookup and dynamic arrays. But before we do that, let me recap what we've gone through with this X lookup function, which is the new V lookup replacement. In fact, it's the new V lookup, H lookup, index and match replacement. Right. There's also X match, by the way, which kind of does something that X lookup does. So X lookup already has X match in it, but I'll explain what I mean. Great. So the first thing we did is the simple exact match. Right. So we checked and saw, okay, what is the exact match? What do we mean by exact match? So that's what index and V lookup does, right? So index will look at the table and say, hey, I need to get dangote's ebit, right? To where is dangote's ebit in this table? If I look at the table, you can see dangote down here. And where's the ebit? Ebit is right here. So there's 108.9, and that's how we got this. Index says, hey, give me the entire table and tell me the exact row to go to and the exact column to go to. That's how index works. Exact table, which row? One, two, three, four, five, six, seven. Well, let's say seven. No, one, two, three, four, five, six, seven, eight. You can count, okay, eight. And then four is the column. So one, two, three, four. So that's how index works. Nice, index is pretty good. V lookup, you lookup value is dangote. The lookup array or table lookup or table index looks at the table after you lookup value and say, okay, which table am I looking for this thing from? And the table is the entire table. And so in this table, which column should I go to? And that's four. So one, two, three, four, column four. And then false is you have to put false there because the default, the default match type is that it doesn't look for an exact match. It looks for an approximate match, which is one of the flaws of the lookup because most people want an exact match. X lookup, very similar. The syntax is your lookup value. What are we looking at? Up, dangote. Where are we looking for it? Well, you give it an array, the lookup array. So this is the where I should lookup dangote from just this column. Then you now give it a return array. So the return array is what it should return. The column where the data contains, the column that contains the data that you want to return. So that's how X lookup works. Yep, just looks at your lookup value, lookup array, return array, very simple. There's also match mode and search mode. So that's a simple use of X lookup. So approximate match, here we're saying, okay, this guy got sales of $17,005. What commission should he get? If you look at this table, look at range in any particular order, we saw that $17,005 is between $15,000 and $25,000. So it's due to get 1.5%. X lookup gets that correct, but Vlookup doesn't. Because Vlookup requires that this table be sorted in ascending order, right? So if I click here and I come to data and I sort in ascending order A to Z, then Vlookup works. But X lookup doesn't care, it will always get you the right value. So that's X lookup far better than Vlookup when it comes to approximate match. And how does X lookup do that magic? Well, after you do your normal X lookup that I just showed you, you now put a comma and then it says, okay, exact match, or you want exact match or next smaller item, exact match or next larger item or wild card match. Well, we still want an exact match, so I'll leave the default there. But then, yeah, so we want an exact match, but actually it's the next smaller item we want. Yeah, so to say it's next smaller item. We don't need the last argument yet. So it's exact match and the next smaller item you close your bracket and enter, and that's how you get it, right? Super. So the next thing we also looked at is last match. Last match, what do we mean by last match? So sometimes most people have data, you have data on products, and your data keeps growing. So the last data you have is really the latest data. This is the latest sales data, for example. This is the oldest sales data and the latest sales data. So if you want to find out how you've grown between January to June, well it's good to get the data for January and then get the data for June and see your growth rate, right? So in VLOOKUP, you can only get the first sale. You can't get the last sale. VLOOKUP always gets the first item. XLOOKUP can get the first item as well as the last item. So what's the magic that it does to get the last item? Well, after your normal XLOOKUP, you put a comma. Yes, you require an exact match. Then another comma and this is now the search mode, right? So you're basically saying, hey, am I searching for the first to last? Am I searching for the last to first, right? First to last is what VLOOKUP does by default. Last to first is the looking at the last item. So you're looking at the last occurrence of that item or that product. Now binary search does the same thing, sort ascending, sort descending, but this is how VLOOKUP works. But we don't need that as in what we need and the way match works, but what we need is that search from last to first, right? Tab, I get minus one there and I close it and that's the code and that's what works, right? So we also looked at, after looking at the last match, we also had a look at a really cool way of doing this, which is using dynamic arrays. So using dynamic arrays, this entire thing we just did, we can do it by using dynamic arrays. And in dynamic arrays, you could just say, hey, let me just delete that. If I delete this, you know that it's just three formulas we wrote here and everything spilled. How is that possible? Well, look at the formula. This formula is a new formula, sorting a unique list of our products. Sorting a unique list of our products. It's a new formula, a new function for only people with dynamic arrays. If you have the latest version of Office 365, you should have these functions. We got to a release recently. Xlookup, well, most people will not have Xlookup, but it's also available. It's going to be available very soon to everybody. So once I get a unique list, if I type something here, you see it can spill as an error. It's trying to spill its answer. Then what about here? So here we're doing the same Xlookup, but we now have this magical hash key. This hash key tells us that, hey, this cell that contains our lookup value contains a dynamic array. So instead, if you look at this, if I delete this hash, you see all it's looking at is just Maggie. But if I want to look at all at the same time so it can spill, I just need to put the hash. But if I leave it like this and enter, it won't spill. If I want it to spill, I come here and put a hash. Of course, I only put a hash if this already had a dynamic array. So I now put my hash key there and then I enter and it spills. Same thing here. This is the last cell using that minus one as our search mode. So that is Xlookup. Absolutely wonderful. So we're going to use the more advanced version of Xlookup. Another advanced version of Xlookup where we have a dynamic lookup. We're trying to build a mini table from a major table. Let's see that next. So a dynamic table. Now I'm going to show you a methodology for how you build this table. So we're going to build out a mini table. And this mini table is going to have dynamic headings. So we can change the headings to anything we want and the tables gets the information for us. We could change maybe the key value. Let's even put something here. Let's say we want a company name. We want the name of the company we're analyzing. We have company names here. We pick them dynamically as well. And then we put headings here and the headings can change at any time and our data should update. So we're going to use Xlookup to do it. Just Xlookup. And let's see how that works. So let's have a look at our data. So this is our data. This is our data with all the headings. This is comparable data on food and confectionary companies in emerging markets. We've got that from sources Damodaran. Damodaran's website. Google Damodaran. You see most people that work as financial modelers or valuation experts, you would know Damodaran. So if you check the headings, we have all these metrics, all these metrics for companies, total equity, revenue, EBIT, EBITDA, to beta, all these data for various companies. How many companies do we have here? Let's see. We have about 260 companies. So what we intend to do is pull a mini table out of this major table. And how do we do that? Well, to do that, we're going to use probably short names. Any name we pick from a list in this short name, and any column we pick in our table, we'll just pull out the data. How can we accomplish that? So if I come to the company, I want to have a list of companies. That's the first thing. So to get a list of companies, I'm going to use data validation. But I want to do it efficiently. I want to do it in a way that this table can grow and everything works perfectly. So what we could do is we come down here and we get a company list, a list of all the company names. And we put it in a single formula. How do we do that? Well, there is in the new Excel calculation engine, we can do equals to unique. There's a new function called unique. And what does unique want? It just wants an array. We can ignore this other two for now by column and of course once. Let's leave that. We just want an array. What's the array we want? It will be the list of companies in our data. But before we pull up the list of companies, if you really want to work efficiently, what I advise is this. You need to name your data. The three very important names you need to give to your data. The very first name is the headings. So the headings of your data give it a name. The key column, the column that contains your unique identifier that you want to use to pull out information, the name, we're going to call this one probably K, right? So in fact, I can call that now. I'm going to pick from the very first name here, short name, the first name on the list, highlight, control shift down arrow key. I come to my name box at the top left here and I call that K. I just call it K. K for key column, right? K. You could call it K underscore something. Then I highlight the headings from serial number, control shift all the way to the right and I'm going to call this headings H, right? Then I enter. Then the entire table, that's the entire table starting from the very first transaction here. I highlight the whole thing, control shift right and control shift down arrow key. There's the whole table. I'm going to call it D and enter. So do this first, then you will really maximize the use of X lookup because now you have references to the entire table. D, you have H and you have K, right? So every single table you have mentioned. So you could say, for example, this is D underscore metric, H underscore metric and K underscore metric and then another table can be D underscore sales, H underscore sales and K underscore sales. So you get what I mean. I'm just using simple DKH here, DHK, right? Great. So once defined, we don't really need this table anymore. We're going back to the dynamic lookup and now you'll see how I can use that DKH definitions of our table to fully build this wonderful dynamic array table. Now we have companies. I want a list of companies. See how easy it's going to be. Company lists, I come here and say, I need a unique list of companies, unique K. All I need is a unique K. What do I mean by unique K? Well, K was the column that contained our key identifier, which is companies, right? So if I say unique K, it spills a unique list of companies. This is the spill to spilled out all the company names. If I highlight this and you check down here, you see count is 260, all the 260 companies, all listed here. Let's go a step further just to make life easier for us to pick a company. Let's say we're going to sort it. So we're going to put a sort. So sort is another array function. When I sort an enter, it's a sort of the entire thing. Remember, this is spill. So everything is written in a single cell. If I type in here and enter, you see it can spill. It's a new error type. Yeah. So if I delete this, it spills. While we're at it, let's also create a unique list of headings, right? A unique list of headings. So let's say unique, unique headings. Remember, we called headings H. We named our headings H in the table. So if I say unique H, you see oops, it's spilling to the right. Obviously, because our headings are to the right, right? But that's not what I want. I don't want this. All I want is for it to spill down. So I need to do something else. I need to say transpose another function. Transpose my spill, right? Transpose my unique list. And I just enter and it transposes it all the way down. Let's even go step further and let's sort it as well so that we can have a nicely sorted list. So let's say sort. So I sort my transpose of my unique headings and I close my bracket at the end and enter. Now it's sorted. So all my headings are now listed and sorted fully. And of course, if I update my table till update here, now that I have my company list and headings, I can now build my dynamic array. So that is how you create sort to just create a dynamic list using the unique function and the sort function. Super. Now to implement my table, I can highlight these four cells come to data. Now I'm going to put a data validation says under data tools. So the data validation is going to be a list validation and what list should I use here? Obviously in the old excel I would have to do this. I'll come here highlight like this. That's old school old school. Don't do that. All I need to do is select the cell, the single cell where I knew I had a dynamic array. So I had a dynamic array there and I just put a hash. See this hash. This is the magic hash. Once you click on the cell that you know you typed a dynamic array into a dynamic array formula. Once you put the hash, what you're telling it is, hey, spill. I want you to use the spill. So click OK. And what happens if you come here? Now I have a data validation that has the entire list. Let's pick some companies. So let's pick various companies. Let's see. Let's go down pick some more companies. Pick this pick company. Yep. Another company. So I'm just picking companies at random. Let's pick the last company. Hmm. Let's see. Sam Young. Interesting companies. Let's see who else. I don't know any of these companies. So that's it. So now I need to populate this with information, right? So what I want to do is I'm going to also do a data validation at the top here. So data validation we have a data. So we have data. I will come to my data validation. Then I'm also needing a list, but this time my list, obviously the source of my list is what is going to be my headings and the source of my list is here. So I click on the first cell, which I know has my dynamic array and I put my hash key. So put the hash key and I say OK. And when I say OK and I come back down, you'll see that now we have data validations. Containing a list. So I'll put a blue box symbol here on the blue box symbol. And then I want, maybe I'll see beta. Maybe not beta. Let's see what do we want? We want eBit. I want to see what else do I want to see? Let's say, like, Bidda is always good. Let's see which metric? EV to a Bidda. That's nice. I say EV to sales, right? Then I want to see what else grow down FF, free cash flow to the firm, free cash flow to equity. And let's see free cash flow to the firm. I need more interesting data. So you can see how easy it is to just pick things. Let's say they return on equity, right? Return on equity, that's good. Great. So now that I have my table all set up and all very nice and dynamic, how do we populate it automatically pulling data from the data set? So we'll see that next. How do we pull out data from the data set using X lookup? Right. So I'll write my X lookup. So X lookup. I'm going to pull out data from the data set. So X lookup. So what's X lookup? The lookup value. Well, this is the lookup value. Now watch this syntax very well because this is a wonderful syntax that would just work for you every day. Super. And it used to be much more difficult to do what we're about to do. So X lookup. You're looking this up. We need to lock the column, right? Because we're going to drag it right. I do a comma lookup array. Where are we looking this up? Obviously you should know that it's in K. You know we defined K, didn't we? So it's going to look up in K. Let me just bring this down a bit so you see that. So it's looking looking lookup array is K because we defined it as the key column of our table. We said we need to define three things in our table. The D which is the entire table, the entire table excluding the headings, then the headings themselves, and then K the key column. So K is there, comma. Now the next thing after comma is return array. Return array. What's the return array? Interesting. So we have this and then what's our return array? The return array well is saying that look this up in K, then return Bloomberg symbol. Unfortunately, we need to highlight Bloomberg symbol. We need to highlight the Bloomberg symbol column in the major table. So it's something like this. We need to come to this major table and highlight this, right? I need to highlight this. And if I highlight that and I close my bracket, I'll be able to get it. But really that's not what we want. If I enter, guess what? I do get it. I get it, right? It's not dynamic. It can drag this right. It's not going to work. So it's just not going to give me the right values. So what do we do? We need to make it dynamic. How do we do that? Well, we delete this and we need to bring in the services of XLOOKUP again. So we do another XLOOKUP right tab. So XLOOKUP another XLOOKUP to give us the dynamic list. So you remember the first XLOOKUP was looking up the company name in K. And now this XLOOKUP is going to look up this Bloomberg symbol, F4, F4, F4 until we lock the row. And guess where we're looking it up? Obviously, you know it's H. We're looking for it in H, right? The headings, right? I want to look for it in the headings. What we want it to return after looking for it in the headings. So your lookup value you have your lookup value here. You have your H, which is the lookup array. And then your return array is actually the entire D. D is the whole table. So D is the whole table. Since we said we're going to H, right? So to look up this H, to get this know the exact column H is in and then give us the entire H column, right? And then if I close the close this XLOOKUP and close this XLOOKUP and enter, I get my Bloomberg symbol. Now if I copy this all the way down, I get everything for all the companies. I copy this all the way for this entire thing. I get exactly what I need to get. I can format it this way. And then now I can do I mean this is just wonderful, isn't it? Look at it. Everything got. I mean I can come here and change this to a bidder. So you see this is a bidder. I now see it's exactly the same as picking up a bidder, right? I can change this to EV to sales, EV to sales. So now I've built a dynamic array. I've built a dynamic table. A complete table extractor. It just extracts information from a table. A mini table. I can change my company all super. Come here and do my other metric. You can say I want the average of this. Let me just lock F4, F4, lock the row. Close that. I can drag this all the way down and come here and make this the median. So the median of it's the median, right? And then I can make this the max, which is the highest. And I can now make this one the min, right? Which is the lowest. And I just drag all this metrics to the right. And there you have it. I have my dynamic lookup table using dynamic arrays at the bottom. I have a list of dynamic array lists using my dynamic array sort and unique. I have my key identifier and I have my headings all nicely listed. And then I use them in my data validation to give me a dynamic list. I use the headings in my data validations. I used edge my data validations. And now I have my bloombox symbol. I can just align that left and I can just pick everything up using this formula which I can just copy and paste all through, right? Copy and paste that all through it works. Just works perfectly. So that is the new and wonderful lookup formulas in Excel. And this is your new keyed on the block which is your X lookup. Pretty super. Yeah. Right. Thank you for watching another webinar from D Brown Consulting. And this is our monthly webinar on Power BI in Excel. And you can always reach us at Power BI PBI user group PBI user group dot com slash Lagos. So I can give you that here. So try and register register and join us. That is PBI user group dot com slash Lagos for our live lab. We do live labs every third Saturday of the month, 9am to 12 noon. And we do most of it. We have sponsors for venue. Our latest sponsors are Lagos Business School. And we have the webinars live labs on live labs, not just webinars, live labs every third Saturday of the month. The webinars every third Thursday of the month, every third Thursday of the month from 9am to 10am every third Thursday of the month. Thanks for watching the guys and we see you next month.