All about the XLOOKUP Function

Intro:
Hello, and welcome to the CPA Australia Podcast. Your weekly source for accounting education, career and leadership discussion.

Neale Blackwood CPA:
In my August article INTHEBLACK magazine, now this was the first magazine that was published digitally only, so there's no hard copy of it, I covered the XLOOKUP function. Now due to space limitations I couldn't actually go into some of the backstory on XLOOKUP, so I thought I'd cover that in a podcast as well as go through advantages with using XLOOKUP. You might not be aware but there is a forum that allows you to suggest improvements to Excel, it's called user voice and you can put forward ideas to improve Excel.

Neale Blackwood CPA:
Now there is a Australian connection to XLOOKUP. There is a Microsoft Excel MVP named Wyn Hopkins who is based in Perth. So Wyn's a great guy, I used to work with him about 10 years ago and funnily enough we were both born in Wales. So Wyn actually still has his Welsh accent, so he sounds a lot better than I do. He came to Australia a bit more recently than I did, I came to Australia when I was two years old, so I never had a Welsh accent. And so if you get a chance to see or hear Wyn he knows his stuff and he sounds really good and he suggested a improvement on the INDEX-MATCH combination.

Neale Blackwood CPA:
So INDEX-MATCH is typically used as a more flexible alternative to VLOOKUP. But the problem is it's a lot more complex, and so what Wyn did was suggest an improvement to that. Now in the all of the changes that happen between what you request and what you eventually get XLOOKUP was the solution, so it was a replacement to VLOOKUP. Now the thing is when it first came out, so it basically comes out to sort of a preview. So when it first came out XLOOKUP had five parts to it, which was an extra part to VLOOKUP. So it had slightly more options to it. So when functions are released, they actually go out to a preview audience and some people can test it and come back with some suggestions.

Neale Blackwood CPA:
And so XLOOKUP was released to the preview audience and someone else came back and said, "wouldn't it be good if we could have an argument that we could specify if something isn't found, because one of the issues with VLOOKUP and INDEX-MATCH is if you can't find it, then typically an error is returned?" Microsoft took that on board and so the final release of XLOOKUP includes the ability to have something that you can display if the entry you're looking for isn't found. So I thought that was a very good demonstration of how agile Microsoft is these days compared to how it used to be.

Neale Blackwood CPA:
And also sort of shows the flow of how you can get new features into Excel with the user voice, and then the feedback that comes back once they sort of release a function. Okay, so XLOOKUP it's included as part of the upgrade for the subscription version of Excel, so this used to be called Office 365, it's now called Microsoft 365 and the subscription version isn't just online. The subscription version allows you to instal Excel and Office on your PC and a number of devices I think. Things are a little bit more complicated now.

Neale Blackwood CPA:
In the old days you had a full version of Excel, typically you installed that off a CD or a DVD and that was your version of Excel and it got upgraded every now and then. But now Microsoft prefers you to go through the subscription version, and that is like an ongoing subscription. You get the features a lot earlier, you can get the features every month and there's also a six monthly sort of upgrade cycle. And if you're in the six monthly upgrade cycle, you should now have XLOOKUP as well as the dynamic arrays, which I covered in some earlier articles and some earlier podcasts.

Neale Blackwood CPA:
So XLOOKUP will not be made available to Excel 2009 team users, that's the last full version. A full version is typically installed off a DVD, something like that and the next full version of Excel will probably come out in 2022 maybe, maybe 2021, maybe next year but you will have to hold out for a while before you can use these new features. So you have to be careful when you're sharing files if people have the older version of Excel.

Neale Blackwood CPA:
So XLOOKUP offers pretty much solutions to all of VLOOKUPs problems. So VLOOKUP is probably for accountants behind the summing functions it's probably the most popular function that accountants use. VLOOKUP allows you to look up in a table, it had a number of limitations. You could only look up codes in the left hand side of the table, so you could only look up to the right, you couldn't look up a code and look up a column to the left of the code, so that was a problem. Also VLOOKUP was reasonably easy to break if you inserted columns in your tables, there were a few workarounds for that. But in general VLOOKUP had a few issues.

Neale Blackwood CPA:
Now XLOOKUP removes those issues and it also makes it a lot easier to use. So as I mentioned, VLOOKUP has four parts to it or four arguments that are between the brackets. XLOOKUP has six, but the first three are required and then the last three are optional. And so you can create a perfectly good working XLOOKUP with just three arguments, which is pretty much the same as how many arguments you had to put in for a VLOOKUP.

Neale Blackwood CPA:
Also the defaults tend to be better this time, so the default for the VLOOKUP was what called an approximate match. So there's an approximate match and a exact match in a VLOOKUP, and unfortunately it defaulted to the approximate match, which is the least used type of match in a VLOOKUP. Most people use what's called an exact match, so you had to put an argument, the last argument had to be included. So for an exact match for VLOOKUP you had four arguments between the brackets.

Neale Blackwood CPA:
For an exact match in XLOOKUP you only need three arguments because it defaults to the exact match, which is really good. Okay, let's take you through all of six of the arguments. The first argument is exactly the same as the VLOOKUP, it's the lookup value. The second argument is where to look in. Now this is either a... Well I should mention XLOOKUP works horizontally and vertically. So vertically is a VLOOKUP. Horizontally is a HLOOKUP, there's also a HLOOKUP in Excel.

Neale Blackwood CPA:
Now I'm just going to talk about the vertical version of XLOOKUP but everything you do vertically in XLOOKUP you can also do horizontally, so it works both ways and it is extremely flexible. Okay, so the XLOOKUP the second argument is the range that you want to look in for the code that you're looking up or the value, and this is a single column in a vertical, well when you're looking up in a normal table layout and that will typically be a fixed reference as well. You give it the look up value, you tell it where to look up and that column can be anywhere in the table can be the first column, can be the last column and anywhere in between.

Neale Blackwood CPA:
Then you tell it the range you want to extract, so this is the range that you want to actually pull the value out of, and again it can be a column, it can be a number of columns. And I'll talk about that a little bit later, but typically you'll just have a single column there. And that may or may not be a fixed reference depends on the type of whether you're copying it and things like that. So they're the only three things that you need, so they're the mandatory fields. So the look up value, the look up range and then the range that you want to extract from, and the range which called the return array.

Neale Blackwood CPA: Oh by the way when you're looking at the syntax of functions, when Excel uses the term array it's really just meaning a range, so it's just a terminology it uses. Okay so three of the arguments are required, the first three and the last three are optional, so let's go through those. If not found is an option, this is the one that was added after XLOOKUP was initially released as a preview, and this particular argument allows you to specify something to display if the value you are looking for isn't found. Now that may be as simple as just displaying a zero, you may display a text, if you do use text you've got to have the quotation marks around any text.

Neale Blackwood CPA:
You can also use an E function in there, so maybe if the look up value cell is blank you may want to do one thing, you can use an E function within the if not found argument to specify different reactions depending on whether the input value cell is blank. If you omit the if not found you will get the NA error displayed, so just a heads up on that. The fifth argument is what's called the match mode. Now this is similar to the last argument in a VLOOKUP, where you can say the type of match that you want to do and as I mentioned it defaults to the exact match.

Neale Blackwood CPA:
So an exact match means that the value you are looking for must be in the table or must be in the range that you are looking in. The approximate match is pretty much for things like tax tables, where you might be looking up an income level but that income level probably won't be listed in the table. But if it's between two other levels, then you grab the first or the lowest level, so that's what the VLOOKUP used to do. INDEX-MATCH, which I mentioned earlier was a bit more flexible in this regard in that you could find the highest value or the lowest value depending on how the table was sorted.

Neale Blackwood CPA:
So INDEX-MATCH was a flexible alternative, but the problem with INDEX-MATCH was it's a lot longer. INDEX-MATCH is actually two separate functions, so there's an index function and there's the match function and you use them together to do a two dimensional lookup. And so you'll have an index with two matches if you're doing a two dimensional lookup in a table. And that creates quite a long formula and it does get a little bit complex, so that's where the XLOOKUP sort of has replaced that and made it a lot easier to do the look ups.

Neale Blackwood CPA:
So the exact match is the default, so you can actually leave out the match mode if you want an exact match, but if you want a exact match or the next smallest you use minus one you can use exact match or the next largest by using a one. The thing you want to remember though if you are using the next smallest, next largest options the range that you are looking in does need to be sorted, so that's something you need to keep in mind. Also the match mode allows you to do something that VLOOKUP could never, and that's a wild card match.

Neale Blackwood CPA:
So all of these I've given examples of these in the article, so if you want to actually see how they work you can check out the article. There's also a companion video. The wildcard match allows you to use the asterisks or some people call it the star, which represents any number of characters even no characters by the way, so just a heads up on the asterisks or you can also use the question mark. Now the question mark represents a single character, so if you put for example a question mark C, then it's looking for a three digit character that starts with an A and ends in a C but you don't know what the middle character is.

Neale Blackwood CPA:
So the question mark will represent a single character. So it gives you a little bit more flexibility, especially if some of your codes have some structure built into those codes. The last argument in the function is called the search mode. Now believe it or not VLOOKUP never looked up, okay? VLOOKUP always looked down. VLOOKUP started at the top and it looked down the table. So even though it was called look up, it looked down the table. The same with XLOOKUP the default operation of XLOOKUP it looks down, it starts at the top and goes down the table.

Neale Blackwood CPA:
Now depending on what of search you are doing, if you want to start at the bottom and work your way up, the search mode in XLOOKUP allows you to do that. So there is the ability to actually look up, so you can start at the bottom and go up the table. So you can actually specify the direction of the look up or the search if you like. So as I mentioned, if you want to see these arguments in action, you can check out the video, the companion video on the INTHEBLACK website. So there'll be a link in the show notes, so check that out.

Neale Blackwood CPA:
Couple of other things that I did want to cover the first is the ability of XLOOKUP to return a reference. Now this is also mentioned in the article, it's actually a bit hard to explain, but here we go. When you use the XLOOKUP normally you're going to return a value that matches the code, so you look up a code in one column and you return a value from another column but the XLOOKUP function and this is something that VLOOKUP could never do, can return a reference to the cell that that value is in rather than the value and then that means that you can use the XLOOKUP function on either side or both sides of the colon operator.

Neale Blackwood CPA:
So the colon is how you refer to a range, so you can have a A1:A10 to refer to a range. Now on either side of the colon you can put the XLOOKUP function and that can give you a flexible way to refer to a specific cell and so that gives you a dynamic range. So based on the entry in another cell you can create a flexible range reference. So obviously you have to use that within a function that's expecting a range something like the sum function. And again there's example of that in the article and in the video. So that can give you a flexible way to refer to a cell reference. Now INDEX-MATCH could do the same thing, so it's giving you that same functionality that INDEX-MATCH had.

Neale Blackwood CPA:
And lastly, I just wanted to cover the fact that you can refer to multiple columns in the XLOOKUP. And when you do you get the benefit of the dynamic array, which I've covered in previous podcasts. So when you return array, so the range that you want to bring values in from, if you refer to multiple columns in that argument it will automatically spill across to include all of those columns. So a single formula can actually spill across to multiple columns if you like to return multiple values. And again it's caught up in the dynamic array feature, so I don't want to go into too much detail on that but it's spilling basically is an automated thing where you type a formula into a single cell and that formula will spill results across, in this case to the right to populate all of the other columns that you've wanted to bring in as the return array. And that's part of dynamic array.

Neale Blackwood CPA:
So again just maybe go if you're not sure about dynamic array, they're a brand new feature as well. And they're again only available at the moment in the subscription version of Excel. Okay, so I hope you've enjoyed hearing about XLOOKUP and sort of how it got developed, and it is a major improvement on VLOOKUP and HLOOKUP, it is easier to use than INDEX-MATCH. So have a look, if you do have the subscription version you can start playing around with the XLOOKUP function. I said it's a much easier function to learn to teach. I hope you enjoy having a play with it. Thanks for listening.

Outro:
Thanks for listening to the CPA Australia Podcast. For more information on today's episode please visit the show notes at www.cpaaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify or Stitcher.