Using the XLOOKUP Function in Dynamics GP

While I spend most of my time in SQL and Dynamics GP, I still have a deep love for Excel. As an accountant, I used Excel for a seemingly infinite number of daily tasks. As a consultant, I use Excel frequently for refreshable reports, exporting data, and validating data quickly and easily.

Often, I find people in GP will export several Smartlists, and use a function called VLOOKUP to include things in one Smartlist that is only available from another. For example, sometimes people will bring in something like the Item Class Code from the Items Smartlist into a Sales Transactions Smartlist. This seemingly simple function always amazes people the first time they see it. It allows them to bring disparate data together with ease.

Over the years, I’ve spent countless hours teaching people to use Excel. One of the first things people need to learn is the VLOOKUP function. It’s always a little difficult for people to grasp at first, but once they understand how to use it, it always becomes a part of their daily routines. It was indispensable until…

Meet XLOOKUP. Not only does this make learning a VLOOKUP-type formula much easier, it is incredibly more powerful. I don’t think I’ve ever been more excited about a new Excel feature, but I won’t wax on about it any longer. Here’s a simple example:

In A2, I have a place to fill in an Item Number. Cell B2 shows the description of the item entered. Often, you’d do this on a separate worksheet, where your data isn’t displayed on the same tab. For the simple examples, I’ve left my data on the same tab.

The formula here is much easier for beginners to understand. XLOOKUP(A2,D:D,E:E). In other words, lookup the value in A2, from the D:D range (column D), and return the value in the E:E range (column E).

Doing this with the old VLOOKUP function would require the function VLOOKUP(A2,D:E,2,False). Still simple for anyone who’s already familiar with the function, but calling out a 2-column range, selecting column 2, and passing the False for approximate match always throws people off.

That’s great, it’s a bit easier, but why all the excitement? Well, you’ll notice that we pass two ranges into the basic formula. The first is the lookup range, the second is the result range. Because these are separate ranges, I don’t have to find values to the RIGHT of my lookup value. I can easily display the Item Description first, like the example below:

We simply switched the order of the two ranges, and the lookup still works. XLOOKUP(A2,E:E,D:D). We lookup the value in A2, from the range E:E (column E), and return the matching value in D:D (column D). This alone is worth celebrating, but there’s even more. We have an optional input for the value if false. What happens, if someone enters an Item Number that doesn’t exist? Normally, we’ll get an #N/A error. However, we can pass “Item Number Not Found”) as a new input as shown below:

I’ll digress for a moment. To do this with VLOOKUP, we’d have to add IFERROR, which is the last function I got excited about (though not as much as XLOOKUP).

With VLOOKUP, we’d need to do this:

IFERROR(VLOOKUP(A2,D:E,2,False),”Item Number Not Found”).

Still straight-forward, but not quite as easy as the XLOOKUP version. Before IFERROR came out several years ago, we had to do this:

IF(ISERROR(VLOOKUP(A2,D:E,2,FALSE))=TRUE,”Item Number Not Found”,VLOOKUP(A2,D:E,2,FALSE)).

This is why IFERROR also got me excited when it was released with Excel 2007.

Before going on to more examples, I’ll show you all the parameters available to you with XLOOKUP.

• XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)
• lookup_value: The value to find
• lookup_array: The array or range to search for the lookup_value.
• return_array: The array or range to return based on the position found in the lookup_array.
• if_not_found: Optional parameter to pass a value to return when an error is encountered (e.g. IFERROR)
• match_mode: Optional parameter to determine how the match is found. 0 is the default.
  • 0: Exact match, like “FALSE” in VLOOKUP
  • -1: Exact match, but return the next smaller item if an exact match isn’t found.
  • 1: Exact match, but return the next larger item if an exact match isn’t found.
  • 2: Wildcard match, using the wildcards shown below in the lookup_value.
    • *: Wildcard for any number of characters. North* finds Northwest and Northeast.
    • ?: Wildcard for a single character. W?n finds Win and Won.
    • ~: Used before a wildcard character to search for the character itself. ~? finds ?

In all cases, we use the lookup_value, lookup_array, and return_array. We’ve also used the if_not_found parameter to replace IFERROR.

Let’s take a look at the match_mode next. The default is 0, or an exact match. If this isn’t found, an #N/A error (or the if_not_found value, if passed) is returned.

-1 and 1 looks first for an exact match, then either down or up for the closest value. The example below finds the closest larger match, by passing a 1 to the match mode:

Even though we’ve only passed part of the Item Number, the description is found by finding the next larger value.

Now, you may have wondered if the lookup arrays can go any direction. For those familiar with Excel, I’m comparing VLOOKUP to HLOOKUP. The answer is YES! You can use XLOOKUP in either direction.

I’ll break the following example down into two separate functions:

The first is actually at the end. It’s a nested XLOOKUP function used to define the return range. XLOOKUP(A3,E:E,G:I). This is looking for our Item Number, and returning the range G:I, restricted for just our row. Now, alone, you’ll get an error because we can’t display a full row in a single cell.

If we add an XLOOKUP(B3,G1:I1, where the return range is our first function, now we’re looking up the site, and choosing the column based on the column name. So, by nesting these functions, and using the ability to choose a column based on the name, we can pass the Item and Site, and return the quantity on hand at that location.

We can also use two XLOOKUP functions together in a range, which can then be used in other calculations, like a SUM function. If you have several locations, and would like to view the sum of the quantity on hand, we could add a starting and ending site, and sum the values across that range:

You’ll notice the XLOOKUP function we used to return the quantity on hand is used twice. Once referencing B3, and once referencing C3. We then put a colon between these two functions to create a range, and wrap that in a SUM() function. The returned value is the sum of the quantity on hand for both sites.

This is an extremely powerful new function in Excel, with myriad of other uses not explicitly described here. Use it, play with it, and enjoy it. When you want to dive into another amazing new feature, you can investigate XMATCH, which was also just released for Excel.

If you have any questions about using the XLOOKUP function for Dynamics GP, please connect with us.