How to use XLOOKUP() to find commission benchmarks in Excel

It’s common to track progress by means of benchmarks. Commissions on sales are a good example of this type of setup. Specifically, the commission percentage increases with the purchase total. For instance, if the total is between $1 and $299, the commission is 3%; if the total is between $300 and $499, the commission is 4%, and so on. In this article, I’ll show you how to use both XLOOKUP() and VLOOKUP() in Microsoft Excel to return the appropriate commission based on the total purchase price. It sounds more difficult than it really is. In this case, neither function proves superior to the other.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. You can use earlier versions with VLOOKUP(), but XLOOKUP() is available only in Microsoft 365, Excel 2021 and Excel Online. There are two demonstration files and both contain both solution sheets. However, the XLOOKUP() functions in the .xls formatted file will show as errors because they’re not supported.

About XLOOKUP() in Excel

XLOOKUP() is one of several newish dynamic array functions. If you’ve ever entered an expression using Ctrl + Shift + Enter, then you’re already familiar with how Excel used to work with dynamic arrays. Thanks to the new dynamic array feature, these types of expressions are much easier to create and maintain because you can enter the expression as you normally would—a simple Enter. The results spill into the cells below, filling as many as necessary to complete the expression’s calculations. That’s called the spill range. If you see a spill error, then the range needed to fulfill the function isn’t available. What this means is that you can use one function to return multiple columns (or rows) of resulting values.

To learn more about this newish function’s syntax and benefits, read How to use the newish XLOOKUP() dynamic array function in Excel.

The problem

Sometimes XLOOKUP() has obvious advantages over VLOOKUP(), but not always. Let’s suppose you want to return a running balance of commissions owed and that the commission percentage depends on the total purchase amount. You’ve probably run into situations where the commission percentage is a set amount, but in this case, the percentage depends on the value of the sale. When working through the requirements, you might think that both values in the commission lookup table are required—if purchase is over this, but lower than that, use x percentage. That assumption might make the solution harder to achieve than necessary, but not because it really is. With either lookup function, you only need to search for one value: the low or high value, but not both.

SEE: Windows 11: Tips on installation, security and more (free PDF) (TechRepublic)

The simple data set shown in Figure A has empty columns for the commission percentage, the commission amount, and a running total of commission earned. The lookup table to the right stores the benchmarks and percentages for the purchase amount groups. Let’s start with a solution using XLOOKUP(). The lookup table to the right expresses the commission groups. As you can see, the percentage goes up as the purchase prices goes up (creating groups of low and high boundaries). 

Figure A

How to use XLOOKUP() to calculate commissions in Excel

Let’s review the commission requirements using real data this time. The first sale value is $1,208. The lookup table to the right shows that $1,208 falls in the 20% commission level (lower than $1,499 but higher than $1,000).

Let’s review the lookup table before we continue. You’ll notice that the low and high values all flow consecutively from the previous level into the next level, and they do so consistently. This setup is important for correct results—no value is skipped. You could swap things around and it would still work. For instance, if the first level high was $400, the low value in the next level would be $401.

Let’s briefly mention XLOOKUP’s syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The only optional argument we’ll use is [match_mode], but first, let’s map out the required arguments:

• lookup_value is the purchase values in Column C.
• lookup_array is the search or source data, which is J2:L7—the lookup table to the right.
• return_array is the percentage values you want to return, which are in column J.

Because the purchase values in Column C aren’t sorted, we’ll use [match_code] to specify the type of match. The default is 0 for find an exact match. Instead, we’ll use -1, which is Find an exact match; return the next smaller item if no match is found. You might also be wondering how a lookup function can return the percentage values when they are to the left of the Low and High columns. That’s one of the new upgrades to XLOOKUP() function; you can reference columns to the left of the lookup column. Now, let’s get to work.

First, enter the function

=XLOOKUP(C3,$K$2:$K$7,$J$2:$J$7,,-1)

in cell F3 and copy it to the remaining cells in that column. Notice that the two array references are absolute; that’s important. If you convert the lookup table to a Table object, your references will look something like the following:

=XLOOKUP(C3,Table2[[#All],[Low]],Table2[[#All],[Per]],,-1)

The #ALL references can be removed:

=XLOOKUP(C3,Table2[Low],Table2[Per]],,-1)

As you can see in Figure B, this function returns the appropriate commission percentage from the lookup table to the right.

Figure B

The XLOOKUP() function returns commission percentages from Column J depending on the purchase values in Column C. Once you have these values, the rest of the sheet is a piece of cake:

1. Enter the expression =C3*F3 into G3 and copy to the remaining cells in Column G.
2. Enter the expression =G3 in H3. Doing so will return the first commission value in the data set.
3. Enter the expression =H3+G4 into cell H4. Doing so will sum the first commission with the second. Copy this simple expression to the remaining cells in Column H to create a running total for commissions.

Figure C shows the completed sheet. The commission and running total columns depend on the percentage commission value returned by XLOOKUP(). You could return a value of high values just as easily, but you only need one lookup column. Including both is great for documentation or sharing with users, but both aren’t necessary to get results.

Figure C

As commission percentages change, you can quickly update the benchmark values (the low, high and percentages). There’s no need to modify the functions and expressions. It all still works. 

If you’ve not upgraded to Microsoft 365, you need to use the VLOOKUP(). The good news is that it’s not anymore difficult. If you do have Microsoft 365, I recommend using XLOOKUP(), but it’s not likely that Microsoft will deprecate the older lookup functions anything soon. Now, let’s see how VLOOKUP() works with this setup.

How to use VLOOKUP() in Excel

You’re probably familiar with VLOOKUP(). It can’t handle a result value that’s located left of the lookup value, but that’s the arrangement we have in the lookup table. (I did that on purpose to emphasize a big difference between the two functions.) Using VLOOKUP(), you must move the percentage values to the right of the low and high values, as shown in Figure D.

Figure D

The VLOOKUP() function syntax

VLOOKUP(lookup_value, lookup_array, column_index, [range_lookup])

is different than XLOOKUP(), but the result will be the same. Enter the following function into F3 and copy it to the remaining cells:

=VLOOKUP(C3,$J$2:$L$7,3,TRUE)

You can see in Figure E, that the function returns the same percentage values. The formulas for columns G and H are the same as before:

• G3: =C3*F3
• H3: =G3
• H4: =H3+G4

Figure E

The completed sheet is shown in Figure F. The TRUE argument finds the closest match, which means we don’t have to sort the data set. In addition, we don’t need both the low and high values to get results.

Figure F

In this case, there’s no real advantage to using XLOOKUP() other than not having to rearrange the lookup table, which could be important. Going forward, I recommend that you start using XLOOKUP(), but don’t worry about changing existing sheets. 

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see