How to use XLOOKUP () to find commission referrals in Excel
Learn how to use both Excel’s XLOOKUP () and VLOOKUP () functions to find results between conditional benchmarks in Microsoft Excel.
It is common to track progress using benchmarks. Sales commissions are a good example of this type of setup. Specifically, the commission percentage increases with the total purchase. For example, 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 seems more difficult than it actually is. In this case, no function is superior to the other.
SEE: 83 Excel Tips Every User Should Know (TechRepublic)
I am using Microsoft 365 on a Windows 10 64 bit system. You can use earlier versions with VLOOKUP (), but VLOOKUP () is only available in Microsoft 365, Excel 2021, and Excel Online. There are two demo files and both contain the two solution sheets. However, XLOOKUP () functions in the .xls format file will show up as errors because they are not supported.
About XLOOKUP () in Excel
XLOOKUP () is one of the many newer dynamic array functions. If you’ve ever entered an expression using Ctrl + Shift + Enter, you already know how Excel works with dynamic tables. Thanks to the new dynamic table functionality, these types of expressions are much easier to create and manage because you can type in the expression as you normally would: a single entry. The results spread in the cells below, filling in as many as necessary to complete the calculations of the expression. This is called the spill range. If you see a dumping error, the range necessary to perform the function is not available. This means that you can use a function to return multiple columns (or rows) of resulting values.
To learn more about the syntax and benefits of this new function, read How to use the new dynamic array function XLOOKUP () in Excel.
Sometimes XLOOKUP () has obvious advantages over VLOOKUP (), but not always. Suppose you want to return a running balance of commissions due, and the commission percentage depends on the total purchase amount. You’ve probably come across situations where the commission percentage is a fixed amount, but in this case the percentage depends on the value of the sale. As you go through the requirements, you may think that both values from the commission lookup table are required:if the purchase is more than this, but less than that, use x percentage. This assumption might make the solution harder to achieve than necessary, but not because it really is. With either search function, you only need to search for one value: low or high, but not both.
SEE: Windows 11: Installation, Security, and More Tips (Free PDF) (TechRepublic)
The simple data set presented in Figure A has empty columns for commission percentage, commission amount, and cumulative total of commissions earned. The lookup table on the right stores the SKUs and percentages for purchase amount groups. Let’s start with a solution using XLOOKUP (). The correspondence table on the right expresses the commission groups. As you can see, the percentage increases as the purchase prices increase (creating groups of low and high limits).
How to use XLOOKUP () to calculate commissions in Excel
Let’s go over the commission requirements using real data this time. The first sale value is $ 1,208. The correspondence table to the right shows that $ 1,208 falls into the 20% commission level (less than $ 1,499 but greater than $ 1,000).
Let’s take a look at the lookup table before we continue. You will notice that the low and high values all move consecutively from the previous level to the next level in a consistent manner. This configuration is important for correct results: no value is ignored. You could swap things and it would still work. For example, if the maximum level for the first level was $ 400, the low value for the next level would be $ 401.
Let’s briefly mention the syntax of XLOOKUP:
= 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 the required arguments:
- lookup_value matches the purchase values in column C.
- lookup_array is the lookup or source data, which is J2: L7 — the lookup table on the right.
- return_array is the percentage values you want to return, which are in column J.
Since the purchase values in column C are not sorted, we will 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 element if no match is found. You might also be wondering how a search function can return percentage values when they are to the left of the Low and High columns. This is one of the new upgrades to the XLOOKUP () function; you can reference columns to the left of the lookup column. Now let’s get to work.
First, enter the function
= XSEARCH (C3, $ K $ 2: $ K $ 7, $ J $ 2: $ J $ 7 ,, – 1)
in cell F3 and copy it to the remaining cells in that column. Note that both array references are absolute; it is important. If you convert the lookup table to a Table object, your references will look like the following:
= XSEARCH (C3, Table2[[#All],[Low]]Table 2[[#All],[Per]],, – 1)
#ALL references can be deleted:
= XSEARCH (C3, Table2[Low], Table 2[Per]],, – 1)
As you can see in Number B, this function returns the appropriate commission percentage from the lookup table on the right.
The XLOOKUP () function returns the commission percentages for column J based on the buy values for column C. Once you have those values, the rest of the sheet is a snap:
- Enter the expression = C3 * F3 in G3 and copy it to the remaining cells in column G.
- Enter the expression = G3 into H3. This will return the first commission value in the dataset.
- Enter the expression = H3 + G4 in cell H4. This will total the first commission with the second. Copy this simple expression into 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 can return a value of large values just as easily, but you only need one lookup column. Including both is great for documenting or sharing with users, but both are not necessary for results.
As the commission percentages change, you can quickly update the benchmarks (the low, high, and percentages). It is not necessary to modify functions and expressions. Everything still works.
If you haven’t upgraded to Microsoft 365, you must use VLOOKUP (). The good news is, it’s not difficult anymore. If you have Microsoft 365, I recommend using XLOOKUP (), but Microsoft is unlikely to frown on the old search functions anytime soon. Now let’s see how VLOOKUP () works with this configuration.
How to use VLOOKUP () in Excel
You are probably familiar with VLOOKUP (). It can’t handle a result value to the left of the lookup value, but that’s the layout we have in the lookup table. (I did this on purpose to point out a big difference between the two functions.) Using VLOOKUP (), you need to move the percentage values to the right of the low and high values, as shown in Number D.
The syntax of the VLOOKUP () function
VLOOKUP (search_value, search_array, column_index, [range_lookup])
is different from XLOOKUP (), but the result will be the same. Enter the following function in 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
The completed sheet is displayed in Figure F. The TRUE argument finds the closest match, which means we don’t have to sort the dataset. Also, we don’t need both low and high values to get results.
In this case, there is no real benefit to using XLOOKUP () other than not having to reorganize the lookup table, which could be important. Going forward, I recommend that you start using XLOOKUP (), but don’t worry about modifying existing sheets.