Excel Hour - Learn to Excel with the FP&A Guy

All right, welcome to anyone who's out there. If you're out there already and you can hear me, please let me know where you're coming from. I can see we have it looks like at least six people in Streamyard and maybe a few on LinkedIn and other platforms. So go ahead and throw in the chat. Let me know where you're coming from. Really excited to have you join us today and we'll get started here in a few minutes. We got a few more minutes before we'll get started, but I'm going to throw up the presentation on the screen. Going to throw it on the screen here. So if anyone needs to download the file, they can go ahead and do that. So again, if you guys can just let me know. All right, looks like we're having a voice issue. Can you guys.

Sa. Can you hear me now? Let me know if you can hear me. Should be hearing me now. So if someone can. Okay, perfect. Sorry about technical issues. That's why I always try to join a few minutes early. Looks like we have someone with us from California so far. Let me know else what where you're coming from. Roxan, Andy, Uziah, we got a few others. So just let us know where you're coming from while we're waiting here. We'll give it a minute or two before we get started. You can download the Excel file there if you haven't already done it. And let's see if I can think of a good Excel joke today. What did the vlookup, since we're talking about Vlookups today, what did the vlookup say on its online dating profile? Seeking an approximate match. It's true. So there you go. There's my joke. We'll give it just another minute or two, let people join. But as a reminder, please download the Excel file. You can go to my website, go to resources and Excel files, the fpna guy.com or you can scan the QR code here and that will take you to where the file is. So we're going to try to make this hands on today. Really excited. We're going to discuss lookup methods and while we're waiting is to give it a minute or two here before we get started. Love to know. Does anyone have a favorite lookup method? The one they kind of default to, they like to use, you know, Vlookup, xlookup, Power Query D, get filter, whatever it might be. If you have a favorite, let me know in the chat. If you haven't already done so let me know where you're coming from. Would love to know. All right, so we'll wait for those comments to come in. I know sometimes I don't get all the comments, so if you commented and I don't respond, it's possible I may not have seen it. You're always welcome to post it again, but I have noticed occasionally that I don't get every comment that's made. But again, let me know what's your favorite lookup, where you're coming from, what your name is, and a little introduction for for those who may not know me, My name is Paul Barnhurst. I go by the nickname the FPA Guy. It's actually the name of my business is the fpnaguy.com I spent a good part of my career in FP and a first I started in procurement, went to grad school, did some report writing, and then switched to fpa. Last few years I've run my own business, sharing thoughts on fpa, helping people be better at it through training by bringing expert voices to interviews. I have three podcasts, FPA Tomorrow, all about FPA Financial Modelers Corner. We talk finance and modeling and then the future finance that I host with Glenn Hopper where we talk about AI and technology. So I love hearing different perspectives and learning how we can be better. So that is a little bit about me. Let's jump into our agenda. So we just did the first one introduction to me. We're going to talk about the different types of lookup methods. I think a lot of times people in Excel just think of one, whether it's Vlookup, xlookup, whatever it might be, Index Match match. And often there's this almost like kind of like people arguing over religion, which lookup method is superior. You should always use xlookup or you should always use Index Match or I like this because I'm one of those that I'm a big believer that it's about the why and then choosing whatever makes sense for you when you understand the pros and cons and the why behind the formula you're using. In fact, one of the things I'm working on, so I'd love feedback from anyone as we go through this is I'm working on putting together a course that goes through all the different lookup methods and the pros and cons and when you might use one over the other and why it's important to understand the differences. You may not need to know all of them, but at least a few of them. So Kim says Well, xlookup, it has a few format constraints here lately. I'm navigating over to Power Query to look up compare. Yeah, I love Power Query. Yes, xlookup does have some constraints. They all do. So thanks for sharing. Anyone else want to share, please put it in the chat. And then what we're going to talk about what are some of the data issues with lookup methods? I'm guessing everybody's done a lookup before and nothing comes back. And you're looking at your data, you're like, I can see that my name's there in the data and my lookup value matches it. Why isn't it coming back? So we'll talk about some of the reasons for that and then we'll walk through a couple different examples. So here's a list and I'm sure we could come up with some others. But here's the main lookups that we have and we'll go into the file here in a minute. But you got vlookup and hlookup, some of the older ones. Xlookup, Index match or index xmatch. You can do a lookup with offset and xmatch. There's actually a function called lookup which was created before Vlookup and hlookup. There's a function called D get and it's one of the database functions that does some unique things. Filter, choose. At the end of the day, aggregate ifs are really lookup functions. They're criteria. They just look up multiple answers and sum them. Whether that's sumifs, countifs, minifsif, max, average. And then you have those non function approaches, Power query table relationships in Excel and power pivot. So many different ways to think about this. So what I want to do now is I want to jump into the Excel file. So if you don't have it open, go ahead and open it with me. We're going to open it to the. To the first page here. And so when it comes, this is going to be some very basic kind of stuff we'll start with. So when it comes to lookups, I like to kind of break it down into three ways to think about it. The first lookup is return a single value or single record based up on some kind of lookup value or criteria. Right? Lookup xlookup Index match is an example. Second one, return all records based on your lookup criteria. Example filter. And then third, return aggregate data based up on based on some kind of lookup value or criteria. Example of sumifs. So If I look here, I have some data. I got my table, I got the top. I think it's 25 cities by population, what continent they're in, what country, the average age, household income, and an approximate percentage that are male. This all came from an AI tool. So take it for what it's worth. It's probably a little dated, but it's directionally correct. All right, so example 1. I really want a simple example of a lookup. I want to return the population for the city listed below. Got city and population here. In this case, I chose to use an XLOOKUP. We could do that probably 10 different ways, but that's the most basic where you typically you have a single lookup value and you're returning one piece of data from the data. Second one is, well, what if I want to return all the columns for Tokyo Again, in this case, I used an xlookup. I did transpose it here so it would go down the rows, but it's given me all the data for Tokyo, Japan. Asia is the continent. The population, the average age, the income percentage is male. So that's where I'm looking up multiple things. I want the entire record. In this case, you know, the only thing I didn't put in here was the rank, which I could have added, but I chose to just do these, you know. Next, this is an example where I'm returning all records that match the following criteria. They're in the continent of Asia. The average age is greater than 30. You know, the household income is greater than 15,000. And so in this case, we're doing a criteria match to return a set of records and different formulas. Some may return a single value, some may return multiple values. Some have the ability to sum, some are designed to sum, and some you have to do it like xlookup or vlookup. You can do a sum, but there's different ways you have to do it. And so that's just kind of a basic kind of way to think about it. So we've listed the talked about these before. You can see that in the file. I'm not going to go through these in detail, but here's all your different methods, right, that you have and a little bit about each of these different functions. So let's walk through an example. When it comes to lookups, really the way they work is kind of one of two ways for the most part, out for functions, there's what I call a positional based lookup. So it's basically working off a position. It's you Got a lookup value and you have a column, it's giving it and it's finding that position and returning it. There's another one that's criteria where you have multiple criteria. It's looking to say which ones fulfill that criteria, Finding those numbers and returning the data that's there. At the end of the day, they're all looking at the data. So here, the way I like to think of a lookup is the table that you're often looking your, your data up in. Think of it as a map, right? If I tell you you need to find something on a map, you're going to go to a latitude and a longitude is often a way to find something on a map. Very simple. In a table, except for this case, it's going to be columns and rows. So here I have six columns, six rows. So what I did is I did a mapping here where I said, okay, I'm going to map my columns and rows. So this is position 1, 2, 3, 4, 5, 6. Problem with that is you can see here is if you just number it well, I can have two 26s, right? In one case, 26 is the number of countries. In another here it's China. And so how Excel works to come up with the number positions is it uses what's called R1C1 reference style. And if anyone's ever played with your options, you're going to notice right in your options under formulas, you can use an R1 C1 reference style and it will change where you have formulas. Come back here. You can see in formulas it will use R negative one right there. And so it's using a different notation. I'm going to go ahead and change that back, but I want to see that's what Excel uses. What you'll also notice that tells you that is look at many functions, like let's take XlookUp as an example. You look at its criteria, not Xlook. I'm sorry, I meant Xmatch is probably the prime example. You have your lookup array. If you not, let's see. Sorry, let's do index and there's a couple others that do this. Notice what it says, row and column. And almost every function, most the time it starts with row. Hence why you see R1C1 Excel. If the formula references a row and column, it almost always starts with the row first. There are a few exceptions, but not many. So if we come back here, right, we could do R1 C1. So then it goes up to C6, then it's R2 so you don't run into this problem here. You could have R5, C2 or C2, R5, depending on which one you count first. But they're always going to return the same position. So that's really what we're doing with many of these lookups is we're saying, hey, you want this column? What number do you want in that column? And it's going through and finding that number. And so that's a little bit about lookups. The other things that you often have to do is you have to tell it a match type. Sometimes Excel will assume an approximate match. An example by default lookup does an approximate match. It doesn't even ask you some of the others, but you're telling it, hey, how do I want to match things? Do I want it to be an exact match? Do I want an exact match? Or next smaller item, do I want wild card? So understanding the different match options is really important because you can get completely different answers. Example, let's say you're calculating sales commissions and you round down. So someone does a hundred thousand in cells, that might be our 110,000 and they need to hit 120 for quota. If you don't round up and you're rounding down, they may get paid at a lower rate. And so you might want to be looking at the next smaller item. So understanding the different types of matching is important with lookups. And what's great is some of the new functions now have the ability to do wild card matches, particularly xlookup X Match, which expands the capability. They also can do smaller or larger where some of the others without a lot of playing with them weren't able to do that. And please, as I'm going through this, feel free to ask questions and follow along. So I'm going to pause for one second, get a little bit of water, and just see if there's any questions so far. In a minute here, we'll jump into the functions and start going through some examples. But I just wanted to show some logic here. So here's a simple example that I was talking about. Sales rep, I have the sales table. I have quota, sold and commission rate here. I have the name of my sales rep, I have the sales amount. And notice here I did an exact match. Won't walk through the formula right now, but I said do an exact match and you'll notice nothing got returned here. Nothing is returned because that number isn't in the table. So in this case, exact match would be a problem. Half the people aren't getting Paid anything. So now if I do an exact match or next smaller item, I get something returned for everything. But Jerry, because there is no smaller item than 2242. If I modified this table, inserted a row and made it zero quota zero sold with the zero rate now, I should get that returned there us being sticky for a minute. It's not recognizing that as a number. We'll, we'll walk through that a little later. But if you, if you set up the table and you had that zero. Yeah, you can see it here. It's because this is an exact match right there. The next smaller Item now shows 0 instead of NA. This one does as well. So you can see how that works. What you're going to then notice, you know, if you're doing larger item, you'll have cases where you might get a different answer if you do an approximate match. In this case, they're pretty similar because they're pretty straightforward. But there are definitely times when you'll get completely different answers whether you do next larger or next smaller. Just like if I take this back out, you could get a different answer with some of them. So next you also have a wild card match that is going to allow you here said, hey, return anything that has the word return. The airlines one. So I know I have a bunch of Delta, I want the one that's speaking to airlines. So I just put a, a star at the front and the back and I end up with 102, which is tied to Delta Airlines Group. So you can do wild card searches. You could also do a search. A single character star is multi character. The last thing is you have search search type. And this could make a difference, especially if you have multiple answers. Do you want it to search first to last or last to first? Right. So an example of that could be here where I have these IDs and I have this date and I'm searching off the date. And in the first one I'm saying first to last. So it's returning Walmart, because Walmart's the first vendor with that date here I said return the last record found. So in this case it's returning Cross Kroger. There's also a binary search which if you have large data and you have it sorted, what it will do is a binary search will speed up the way it processes, it will run the search faster. So if you have, you know, thousands, tens of thousands, hundreds of thousands of records, a binary search could be really helpful. So those are the basics of kind of how to think about this and how to think about lookups. The next area I want to talk about are what are some of the common data pitfalls? And I'd love to know has anyone ever ran a lookup and had something not returned when they, when they could see that it was in their table and wondered why anyone ever dealt with that one? Love to know in the chat. Let me know if you've ever had that situation where you're like, but they look the same. Why isn't it returning anything? Well, basically there's really what I'll call four. There's four situations here where that tends to happen. The first is there's a mismatch between the data type. The, the, the, the main culprit is one is text, the others is numbers. We'll talk about how you solve that. The second one is what I call extra spaces. So trailing or leading spaces, you might have the word jets and you have a bunch of spaces at the end. In the other place you don't have any spaces. Not going to match. Yes. Kim just said always happens if you extract from QuickBooks. Thank you, Kim. That's a great example. Often happens when you exp extract from databases. So first is the data type. Second is the extra spaces. Next is what we call non printable printable characters. And I'll show some examples of that. And so you have non printable characters. And then the last is you could have multiple, you could have data type extra spaces and non printable characters. How do you clean all those up so you don't have to constantly deal with that issue? So let's talk about the different types and let's walk about, let's walk through how we would fix them. So the first thing I want you guys to realize, if something is text, it will be left aligned. Notice all of this is left aligned. If it's a number by default, unless you make a change, it's going to be right aligned. That's the quick and easy way to identify something. So let's just show an example of that. Say I come over here and I do 1,0,01 with a dash in front and then I do 1 0,01 without it. You can see this is left align. That's text. That's a number. If I use the formula this, does this equal this? It's going to return false even though on the surface they look the same. Right. So here we're going to do something here. So you can see it right here. I, these numbers right now are aligned. But I could, I could change that So I have 1002 here and it's left aligned. But let's say it's, let's say it's right align. So what I want to do, Give me one second here. We're gonna. That should. Ah, let me refresh it now. Yep. All right. So now we're going to put in the number 1002 and so you can see there's no match. So now that's right aligned. If I wanted it to match, I would have to change it. And right now it doesn't like that. So what we're going to do, I'm going to show you a trick here. Under data validation, if you want to be able to override a list, you can say show error alert after invalid data is entered. Turn that off. And then you don't have to put the data from your list. So here if I change this back to 1002 on the left side, I get a match. If I Change it to 1002 on the right, I get no match because my data is all left aligned. So that's the first issue. So what do we do? How do we fix that? There are multiple ways to convert text to a number. You can even convert a number to text, but we'll start with text to a number. So right here I have a text column. So what we're going to do is notice here if I put a double negative in front of now becomes a number. The reason for that is any math operation will turn something that looks like a number but is formatted as text into a number. So I have negative, negative here. Some others I can use. I could use the value function and it converts it. And this last one here, and I'm just expanding it so you can see it is right align is. I could times it by one. So perform any math operation and it will convert to text. The reason I show these math operations is it doesn't change the number, right? So that's one way you can do it. Next is method 2. 2. You can do error checking. So I can highlight this. I can come up to my formulas. I can say, hey, do error checking. And when I click on it, it's going to say, hey, we noticed this is text. You want me to convert it to a number and I could do all these. Or there are some times where there's ways you can do all of them at once. But that's the second you could use error checking. One trick I like with a lot of data is text to columns. So here I could come up here I can go to data, I can say text to columns and just click finish. And if it's all text that looks like numbers, when it runs that operation, it will convert everything in that column to a number. Now what if I wanted this number to be text? Well, you know, I could, I can do a question mark with that column and I can end it with a question mark. And why did it not? Come on. Why? Give me a second. It doesn't like something here. Let me see. Text something in there that it's not liking. So let's do, we'll do a different example. But generally if you put quotes around something, that is another way that will convert it to text. So in your formulas you can like this number right here. You could come into the formula and make force it to be text. I could add this right here and notice the answer returned. So I said put double quotes around it and combine it with the number and it converted that number to text. But it didn't change it here, it just converted it in the formula. So you could do a double quotes and an ampersand to a cell. So I have to think about that for a second. So, right, so if I come here and do double quote and. And you can now see it's left the line double quotes implies it's a text string. So that's the fourth example where you can go through and modify it in the formula. I generally don't recommend doing that. I always recommend kind of fixing your underlying data versus changing in a formula. Maybe you're in your hurry, maybe you have time to fix the data. You can always do stuff in your formula to convert one side or the other. You can apply it to your lookup value, you can apply it to your table range. So that's the first misalignment that we run into. Second is leading our trailing spaces. And so here I'm getting no match. And the reason is see which one of these has the leading. So let's see. Oh, okay, product id. It's not matching because there's a leading or trailing space. But notice here, same thing, I trimmed it. So I have a trim table here. So this table right now has a space at the beginning. So what I did is I apply the trim formula, what the trim formula will do. So let's just take an example. I'm going to do three spaces, type the word Paul. Put three spaces at the end down here. I'm just going to type the word Paul right? If I do this, it's going to return false. So not the Same. But if I do trim around each of them, it's going to give me a true. So the second one is leading our trailing spaces and wrapping something in trim will eliminate that. The next one and I want to show this. So there are different characters, line breaks, carriage returns, other things that are called non printable characters. Notice right here, all of these show nothing. They all show blank when you look at them. But right here I'm returning the formula. They all have a formula that says char in them. Next, look at this. If I return the length of these characters, they all say they have a length of one. So even though nothing shows up, something's there. That's probably one of the most frustrating ones we've dealt with, particularly QuickBooks and others. So if I come here, I've combined the five with the character and even though none of them show the character, my data length is 2. If I ask it if this 5 is a number says no, it's not. So the function we can use is called the clean function. Clean, generally, with few exceptions, will get rid of your non printable characters. There's only two it doesn't get rid of. So you can see the 20 here. Now if I look at the length, all but the ones highlighted in yellow are a length of one. So clean is a great way to get rid of non printable characters. If you have one that doesn't work with clean, you use substitute and all you do is you substitute whatever your character is. In this case I substituted them all at once. But you could select that individual character, you could substitute it with nothing. So you can see in that case my length is all one. It fixes those that a clean does not fix. Pretty rare you're going to run into that situation. But you might. So here at the end of the day, kind of bringing it all together, you know, I do a normal look up here, it doesn't work because this is right aligned, left aligned. If I perform my math operation, it's still not working. It's like, okay, why is it not working? Well, there must be something else. So now I do a trim. I've done a math operation and a trim all on my lookup. Id still not, still not working. Here I now do a, a clean. I do a math operation, I gotta clean a trim. So first it's trimming it, then it's cleaning it, then it's converting it to a number. Once I do all three, it returned the answer I wanted. So what that tells me is I got spaces, I, I got non printable Characters all in my data. And so I see some people that will always do a clean, a trim and some kind of math operation, whether it's value or double, negative or whatever. And you'll see in this case, I did it to the data set here. What I should really do is apply it to the data and fix the data. But I'm showing you can do it wherever you want. You can clean your lookup value, you can clean your data, ideally always fix it underlying versus the formula. But I want you to understand the method. So I'm going to pause here for a minute. Next, we're going to start going through some different lookup functions and showing how they work. Does what I share so far make sense? Does anyone have questions like any situations where they've ran into, where they're like, I ran into these leading zeros or whatever and trim didn't work or another method they use? So just give a minute and any comments from anyone. It's been very quiet. Even if you just want to tell me where you're coming from, see if we get any. All right, we got a quiet group today. All right, so let's jump into lookup functions. I don't know if anyone's ever used the lookup function. Not xlookup, not vlookup, not hlookup. There are actually two different versions of lookup. And so if you type in, go ahead and do it with me. We got Portsmouth out there. Thank you, Stanley. Somebody let me know. They're still hearing me. I appreciate it. If you type in the word lookup, notice it has this lookup value, lookup vector, result vector. And then it has a second type of a lookup, lookup value and array. If you do the same thing with index, you'll notice it has two, basically two different functions. This one is array, row number, column number. Second one is reference row number, column number, area number. The second one is incredibly helpful. We won't get through that one today, but I'm going to be launching a lookup course and I will go through it maybe in the future, se. But lookup has two. Basically, I want to focus on syntax. One, very simple. It has lookup value, right? That's the value you want to look up. And then you give it a lookup vector, which is basically your range or your array, and then a result vector. One thing it does is it doesn't ask for your match type. It automatically assumes your data is sorted in ascending order, and then it always performs an approximate match when no match is found. So what are the pros and cons of of Lookup? Probably one of the biggest is right, it works in all versions of Excel, but there are a couple others. It's really simple. It can work both horizontally and vertically. You know, it requires less computational resources because of its simple nature. So there may be times, if you're dealing with big data, it will run fast, faster. So maybe it makes sense. Another advantage, your lookup value doesn't have to be in the first row like Vlookup or Hlookup. Some of the limitations, it's limited to a single row or column. It only returns the first matching value. It is a little less flexible than some of the newer functions. And as a general rule, unsorted data will cause issues. So we're going to look at one example of lookup, just a vector example, then we'll move on. So follow along here with me. You guys can try this together. But what I've done here is I've written a simple formula. So we'll write this. I want to look up based on this test date and return the score. So if I come in here, I type lookup, I put in my lookup value. I put in my vector is what they call it this, which is basically your lookup array or range. I put in my return, I want to return the test score. And that's it. Really simple function. Easy way to think of it. Well, my lookup column, what's my return column, what's the value? I'm looking up? I'm done. Gives me a 76. What would happen if this, you know, if I change this sort order so it, it's not going to work. And we'll show that later. So that's, that's the first one. Second, same idea, but here I just change it to the test rate. So I said, hey, look up this. And what I like about Lookup over Vlookup is it's similar to xlookup in that I don't have to worry about what column things are in. I can just tell it the columns I want. So this test date could be the last in the last column and it would still work. It could be in the middle. It can be wherever you want. Next, I just want to show kind of as you start learning more and more, there's cool tricks you can do. And I'm going to show two. So this first one, return the test score based on the last test date. So here, kind of just to show as you start to learn more and more, there's many different ways. I'm looking up the number two. Then what I'm doing is I'm taking one divided to test score where it's not blank. So what it's doing is it's looking at every single one of these and if it's blank, it's going to return a division error which is false. If it has data in it, it's going to return a one. And so it will go through find the first blank in your data and it will return. If it doesn't find a blank, it will return the last in the record. If it finds a blank, it will go to the prior one which is your last one and return that. So there's cool. That's something that lookup is better apps and say like a Vlookup because of the way it's built. That might be a situation where hey, because I know how it works, I want to return the last date regardless of how many dates are there, even if a bunch are blank. So if I go here and add a bunch of records, it still returned that last score because it's looking at and saying, oh hey, all those come back as false. I'll go find the last one in my set. And then one thing I want to want to show is just the more you learn Excel, the more you can. You can often overcome many of the limitations of a function Vlookup. Everybody's like, well, always has to be in the left column. There are ways it can be in the right column. Oh well, I can't do a two way lookup. There's a ways you can do a two way lookup with Vlookup. Learning the functions will allow you to do things like that. So one of the things lookup is known for is not being able to handle sort order. So what I did is I deliberately sorted these in something that is not in an ascending or descending order. So right. Notice name. It's Phil, Jill, Mark and then Jim. Notice test one. There's no sort order there. I, I deliberately created a random sort order and sorted it by that. So if I do a typical lookup, what's going to happen? And what I did is I did a a lookup where I wanted to be able to return multiple columns. So I said hey, look it up based on the name. So I got D27 Phil. Then I have the person's name here. And then what I did is I used the choose and I said I'm going to give all four columns and I'm going to choose the column. What I want to look at. So I created a two way lookup. But what I found is it still broke in certain situations. If you look here, Phil, for test score three should be 67, but it returned to 76. But I then said, hey, what if I sort all of my chooses, I can get it where sort order doesn't matter. So even with a function that's really old that says hey, you can't sort things and it has to be in ascending order and it has to be a one way lookup, we just did a two way lookup and we just fixed all the sort order right in the function. The more you learn, the more you can realize there's different ways. You can often overcome the challenges of different formulas. Not to say you should do it that way. I would use an xlookup or some other method here. But because I was able to spend some time, I was able to learn, oh hey, I can really get the right answer right. We can see Phil here. Test two is 78. If I change this to Jim, I get the right answer. So that's that. Next is dg. Git is a functional. People either kind of use it a lot and love it or they never use it. I will, I'm familiar with it. I'm kind of in that never use it. It has three arguments. Your database, your field and your criteria. And the reason it's called DG is D stands for database. There are a number of functions in Excel or the design to work when your data is stored in tabular format or like a database, so it's in the proper table format. DGIT is a pretty cool function. So some of the pros and cons are listed here. In the interest of time, I think I'll skip reading through them. And I'm going to show you an example of how some different ways D get works. So here we have dgit. I have an employee name. I've typed in Liam Chen. I have her department. And so what I've done is I want to find the department for Liam Chen. So I've used the D get to return her her department, her job title and her salary. How dget works is if you remember there were three arguments. There's the database, the field and the criteria. So we come back here. Here's my database. So the first thing I did is I highlighted my entire table. That's my database. So if I retype this formula, that's what I would put in first. We'll redo it in a second. Second you could see I put C6. C6 is the title for department. So that's what I want to return. The field I want to return from this data is department. And now, what's my criteria? My criteria is employee name. That name is Liam. So what it's doing is it's saying, okay, here's your table. Here is what you want to return. I want to return the answer to the department label. So I have to type the word department. If it doesn't exactly match, it won't work. And then the last one for criteria, I'm telling it what column, what label to look at, and who to look for. So you'll notice I have to put both in. I have to put the label and the person. So if I type another person. If I type Abigail more, and I notice I forgot the E and it still found her. That's one of the cool things about dg, is it will automatically do an approximate match and go through the list and find the person and return it. So I could put Abby here. And it looked and said, okay, we believe that's the right person. Now, there are a couple drawbacks as well. So you can see here. This is an approximate match. Before I get to drawbacks, I want to show one other example. Let's say we had Emma Watson, and she recently got married. And we're not sure if the report picked up whether she got married or not she married Emma Radcliffe. So check this out. Let's look at the formula here. I'm going to bring this down one minute, and we're gonna. I want you to see what happens here. So we'll bring that down. I'm going to bring this down to here. Notice it found Emma Watson. So it will do an. Or you could add, hey, find Emma or the other person. Then if I drag this over, we can see all. All the information for Emma. So, right, you can see that there. You can see here. Here. It matched. Now, let's go look at our data. Right. We can see Emma Radcliffe, but what I want you to know is what happens if we have both in here? So let's come down to the bottom. Let's add Emma Watson. So for whatever reason, they're both in the. In the data. If that happens, it will return a numeric. If you have duplicates of something, it will not. It will not do it. So your data has to be unique. The other thing that's a real weakness of dget is I can't. I couldn't type a second person's name here and drag the formula down. You're looking up one record at a time from a database. I know some people that love that use it all the time. Others think it's awkward. But last, let's do xlookup. So I'm curious as we're getting ready to do this, anyone have a favorite lookup? What's your go to? Are you someone who likes Power Query Index Match? I know some others have mentioned anyone else will willing to share? All right, hopefully somebody will share for us. If not all good. So let's go to xlookup. What I want to show here is these are probably the three most common xlookup, Vlookup, Index Match or as I like to say, index xmatch. So first what I want you to notice is we have these bonus ratings. It says look up bonus payout on rating and return an exact match or next smaller item. Stanley's a power query person. I am as well. Stanley. I love Power Query. We got a LinkedIn user index match. Yeah, I, I like, I like Index X Match. There's definitely use cases. I probably lean towards an xlookup myself. But they all, they all have their use cases. All right, so first thing we'll notice between the three of these, notice we get an NA here, we get no payout here. But otherwise they've all given us the exact same answer. What happens if I change the sort of my data notice Vlookup broke. Those are things to be aware of. It needs to be sorted a certain way or you have to do things like I showed under, you know the lookup function where you have to write some complex formulas. Other things that I want to show you is what happens if we change the table. So I'm going to change this to be payout. I'm going to change this to be bonus rate. And so I'm going to take this, copy it here. Right. All of them are going to break. But what I want to show you is the reason they broke is we have to change the names. So if I was to change this name now and say, okay, I'm matching that, so this should be bonus rating and I change it, we'll be fine. Right? Bonus rating and payout. All good. It's gonna work unless I type something wrong, which it looks like I did. What did I type wrong? Oh, I typed bonus rate. So let's just, we'll just switch these. Bonus rate and payout. Right. All good. Doesn't care that this is to the right of this here. What's going to happen is it's not going to work because it wants bonus rate in the first column. So that's a. That's a challenge. With Vlookup, you'd have to use a choose or choose columns to basically in your formula, change the order of your columns, which you can do. I could make Vlookup work, but as a general rule, it's going to break. Index match will be fine if I just change these columns here to be the reverse. So this now would become bonus rate. And here it's looking up the word payout. It all works. So let's walk through kind of how they work to kind of understand a little bit. We just got about five more minutes here and then we'll wrap up. So how xlookup works, and what I really like about xlookup is basically it's a new version of Lookup. It is closer to Lookup than it is Vlookup or Hlookup. And what I mean by that is you first put in your lookup value. Then you tell it, where's my lookup column? Well, that's right here. That's my array. Then you tell it, where do you want my to return the data? That part is basically the original lookup function. If I change that X to a lookup and just closed it, we're done. But what they did is they added a bunch of additional arguments that makes it more powerful. First they said, hey, everybody's always wrapping this stuff in. If error, why don't we just give you what it should return if nothing's found, that's an argument. I'm putting quotes around it because I want it to be text next. Well, what's my match mode? Do I want an exact match, exact match or next smaller item, larger item, or do I want to do a wild card match? Right. If you remember, with Lookup, it automatically just assumed you always wanted to do an exact match or next smaller item and your data would always be in ascending order. Here I can tell it how to sort the data first to last or last to first. We'll just say first to last. And so you can see it has a lot more options. If I was to add 60,000 down here at the bottom as a payout and the number five, you're going to notice it's still selected 50,000 because I told it to sort first to last. If I change my sort order now, I get 60,000. So XLOOKUP has some additional flexibility, especially compared to Vlookup Index match. The way index match works and the way I like to describe Index match and we'll end here is Index match is basically kind of like a map. You give it your map, here's my map, and then you just tell it what positions you want. So here I could manually say, okay, five is row five and I want to return the payout, which is in column one. I got my, I got my 50,000. You know, if I copy that down, I get 50,000 again because, oh, a four. Well, that's row four. So all you're doing with an index is you're saying, here's my, here's my table, here's my map, here's my row position, here's my column position. What the X match does is it returns that position. So all a match is doing is it's saying, and I like to use X match. Is it saying, okay, what's your lookup value? My lookup values to 5. Look it up here. Do a match mode. We'll do an exact match. And then this one we'll just, we'll. So we've closed that and we've made it dynamic. And for the second one we know it's always going to be column one. So I just typed in the number, I could make it dynamic as well. But all the X match does is it returns the position. That position then gets returned he to here and it finds the number. So if I look at this, if I was to come and highlight this and look at what it's returned, you can see right there turning the number five. So that's index match. And you'll see there's a couple other examples in here. How do you build a nested X lookup? You could do a two way lookup with xlookup, just like you can with index match. So where I want to close is for me the key with lookups is just learning the pros and cons, whether that be power query table relationships, xlookup Index match. Next time somebody tells you you have to use this version, no, just tell them they're wrong. You don't have to use a certain type. You might have a preference. But as a general rule, multiple different methods will work. It's all about learning the pros and cons and then knowing what works. The example I give is often we just always use Vlookup, let's just say as an example. So we're always forcing our data to match the function. Whereas if we spent the extra half an hour and learned that additional function, we might not have to make any forcing of our data. Tom and that could save us a full day over the year, but it's going to take us more the first couple times. That's really the idea here of why you want to learn what method makes the most sense. So in closing, we're going to go back to the PowerPoint deck here. I'm sorry, it's got an error message. And as always, I just want to mention two courses that I have. I have the F an FPA ultimate course bundle, which I would really say it applies to all finance people, has business partnering, modeling, design principles, Smart Value through data analysis of modern Excel. That course typically runs 129. If you use the code webinar, you'll get 30% off on that. You can see the QR code. This should be. It's 30 at the moment and you can see what people are saying about the course. So that's the first option. The second is if your focus is Excel and you want to go a little deeper. I have a course for that and I'm going to fix that. It's not 300% off. It is 30. Be nice if it was 300, but then I'd owe you money. So 30% off of the $60 if you use the code webinar. And what I want to say, if you buy the course now, I'm going to be adding these new lessons and modules and as they are added, you will receive free access to them. So as I add more to the course, you'll get that. So just want to put those two options out there for anyone who's interested. And I want to just close by saying thank you for joining me. I hope you learned something today. And, you know, we'll do this again in a couple. We'll do this next week and then I'm off for vacation. But if anyone has any questions, we got a minute or two here, let me know. All right. Doesn't look like we're seeing any questions, so I'll just say thank you for everybody that joined. Appreciate it. And I'm going to go ahead and end the stream, so you guys have a good day.