Xlookup - How to write a nested Xlookup function with The FP&A Guy

FP and a guy here today we're going to talk about xlookup. In particular, I'm going to show an example of how you do a nested xlookup. This is a request from a couple people on LinkedIn, including Chris Riley, who asked about how you would do that. So I'm going to walk you through a nested xlookup and then a couple other complex examples. If you're looking for a beginner video, I recommend going to YouTube and looking up some others. If you're looking up for something, a Vlookup index match what the difference is, again, go ahead and go to YouTube. There's a lot of great videos. Today we're going to focus on some more complex concepts around xlookup. So let's get started. So the example we have here is we have these employee numbers, we have this bonus ratings and we want to calculate the payout. We also want to return the description and the employee code. So what I'm going to start here is just show the basics of xlookup. And you'll notice on the right hand side I have the xlook lookup file function with the description, the syntax and a description for each different part that makes it up and what the options are, including which parts are optional. So if you want to understand the formula, you can download the spreadsheet and read that. But let's get started. So xlookup here I want to do a lookup value and we're going to look up the entire range at once because we can do that in 365. We're going to hard code it here. Then, then what I'm going to do is I'm going to select my lookup array, which is my bonus rating. Again, I'm going to hard code it. Next I'm going to do the return array. So the return array in this case is I want the payout. So I'm going to go ahead and we're going to hard code that. In this example we're going to say if something's not found, return the word not found. We are going to do an exact match or next smaller item. So those halves, the three and a half, the four and a half, the two and a half get paid, get paid something, but they get paid the lower number. And we'll go ahead and search first to last. We'll go ahead and close it and we can see here the things got returned. Well, what if I want description? Well, if I drag this over, I get the same number. So sure, I could Come on in here and say, hey, give me the description for this right now. I get it. And if I drag this over again, doesn't work. I could come in and say, hey, give me employee code. Or I could try to change the sort order and make this formula the second one not be absolute, but be relative so it drags properly. But that's a lot of work. How could we make this dynamic so that when I drag this across, it automatically finds the right columns, similar to an index match? Well, what I can do is what's called a nested xlookup. So here, under the second return array, I add a second xlookup. And what I do with that xlookup is I do this lookup value. So I want to look out the payout. I want that to be relative. So when I drag it across, it works. And I'm going to look it up within this entire range. So I hard code that. Now I put my return array, and that's my return the entire range. Now I can go ahead and say, hey, not found. I can do my match mode. We'll say exact match or next smaller item. And we'll say search first to last. But that only applies to the first xlookup. Now we need to do the same for the second xlookup. So I'm going to say not found Here, going to add my match mode, which again, I'm going to have it be the same and a one. And so now let's see what happens when I drag these across. I dragged it one more than I needed to. So we'll delete that. You can see I now got the right codes for all these. We'll fix the formatting there. I now have the employee code description and the payout. And what's great is if more stuff was added to the table, you know, names were changed. So if I change this to code now, that no longer works. It gets confused. But if I change this to code, it now pulls the right number again. So that's how a nested xlookup works. The idea is, and the way I like to do this formula is I like to put this on a second line so that you can see the formula broke apart. And then I'll put this on a third line. So what you can kind of see is, all right, there's the xlookup. This whole part here is the return array, and then this is the ending of the formula. And you could indent them, but that is a nested xlookup. Let's look at a couple other Examples. So example two, we have three cases here. I want to return the volume and gross cells corresponding to the SKU name and period. So I want SKU number 001 for period one. I want to return the volume, the gross cells. So let's see how I would do that. So in this case, this is where you have to concatenate to make this work. So what I'm going to do is I'm going to say xlookup and I'm going to take this and this. So I want to find both together. Now what I'm going to do is I'm going to come over here and this time we're going to be working with tables. So you're going to see table names. And we're going to have the period here. Right. So I've got that. And now what I want to return is I want to return the volume. I'm going to say return this column here, close it. You can see I can take it all the way down. In this case I didn't highlight the whole range. I could have, I could have said, you know, H like this. I could have gone here, return the whole thing. You can see I get a spill error because there's something in the way. So if I delete this, it returns the numbers. So what I did is I basically treated it as if it was one column by combining together. But what happens if I drag this over? It doesn't find the right answer. So there's a couple things I didn't set up right. What if let's go through and hard code these. Let's say all this gets hard coded. Does that fix it? What happens? It does not because you'll see volume, all these SKU names automatically moved one because they're relative. So what would happen is you'd have to do an absolute and basically relative references. So I'll show what I mean here. So let's come down here and look it up. So here you'll notice I have H17 and I17. So I have the first column work again. We're going to change that to be the entire range so I can answer it all at once. And then we'll delete this out. So notice. But notice what I did here with data product skew is I put a double brackets around it. I have another video that explains that. That makes it an absolute reference. And I made the period absolutely. But what I did is I made the volume relative. And since I have these in order volume EBITDA gross. When I drag this over, it will automatically pick up the ebitda. See, it did there. Now, since I just dragged that without fixing the formatting, the formatting was off. But if I drag this over again, you'll notice it's going to pick up the right number, right? It picks up gross cells. So there I've now done something where it's a table reference. It's absolute and relative. But the one problem that happens is it's not dynamic. If this was gross cells here, the number doesn't change, right? I didn't get the right number. It still gave me ebitda because EBITDA is next to volume. So how do we create the dynamic? Again, that's very similar to what we did before, just with the table is we do a nested xlookup. So you can see I combine the H and the I. I combine the data products in the period with the double brackets. Then I did my next one here where I said, hey, give me volume unit to gross cells. So give me all three. Then here I said, do the same. So it's looking up both those ranges. One's the headers, one's the data. Now, when I change this, it will automatically adjust what number it gives me. So you can see, I could get volume, I can get ebitda, I can get gross cells. I could change a period here and it will change that number. See, it's the same as there. I could change a SKU here. And so now I have one that's dynamic that can give me whatever numbers I need. And so now let's go through and do the last piece. This is a sum. So if you want to do a sum xlookup, you can do that as well. So what I have is I have SKU name two. Starting period is one, ending period is six. And I want to see the total volume. So first let's just confirm that's working. So let's select this and let's see what the total is. The volume for those periods. 5147. All right, so now let's clear that and see if we got the right number. 5147. So what you can do is you can put a sum around XLOOKUP. And what I've done here is I've done two XLOOKUPs. So I do the first XLOOKUP, which you can see I 35 and I 37. So I 35 and I 37. Or sorry, not 35, 36. Yeah, I 35 and I 37. You could see what I get back. And then you can see I do the second one, the i35 and the i36. And what it's doing is it's selecting everything between the range of one to six. I've done this lookup, looking up the entire range from the start to the end period. See, if I Change this to 9, the number adjusts. If I change it to 12, I change it to 1. You can see I get just, you know, basically the SKU name two for the first period, the 1000 in volume. And so I can change this and dynamically get a sum for xlookup. So there's a great example of different ways you can use xlookup. I hope you enjoyed this video. Thanks.