Excel: How to Use XLOOKUP () to Find Fee Benchmarks

Excel: How to Use XLOOKUP () to Find Fee Benchmarks

It is common to track progress via benchmarks. Sales commissions are a good example of this type of setup. Specifically, the commission percentage increases with the total purchase. TO

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 will 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.

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

Learn about XLOOKUP () in Excel

XLOOKUP () is one of several new dynamic array functions. If you’ve ever entered an expression using Ctrl + Shift + Enter, then you’re already familiar with how Excel worked with dynamic arrays. Thanks to the new dynamic array functionality, 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 do pour out in the cells below, filling in the number needed to complete the expression calculations. This is called the spill interval . If you see a deposit error, the interval needed to fulfill the feature is not available. This means that you can use a function to return multiple columns (or rows) of resulting values.

For more information on the syntax and benefits of this new function, read How to Use the New XLOOKUP () Dynamic Array Function in Excel.

The problem

Sometimes XLOOKUP () has obvious advantages over VLOOKUP (), but not always. Let’s say we want to return a current balance of the commissions due and that 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. When looking at the requirements, you might think that both values ​​in the fee lookup table are mandatory: if the purchase is greater than this, but less than that, use x percentage . This assumption could make the solution more difficult to achieve than necessary, but not because it really is. With both search functions, you only need to search for one value: low or high value, but not both.

The simple dataset shown in Figure A has blank columns for commission percentage, commission amount, and a subtotal of commission earned. The lookup table on the right stores benchmarks and percentages for purchase amount groups. Let’s start with a solution using XLOOKUP (). The lookup table on the right expresses the groups of commissions. As you can see, the percentage increases as purchase prices increase (creating groups of low and high limits).

Figure A


The lookup table on the right determines the commission percentage.

How to use XLOOKUP () to calculate commissions in Excel

This time we look at the commission requirements using real data. The first sale value is $ 1,208. The lookup table on the right shows that $ 1,208 is within the 20% commission level (less than $ 1,499 but more than $ 1,000).

Let’s examine the lookup table before continuing. You will notice that the low and high values ​​all flow consecutively from the previous level to the next level and do so consistently. This configuration is important to get correct results: no values ​​are skipped. You could swap things and it would still work. For example, if the first level maximum was $ 400, the minimum value in the next level would be $ 401.

Let’s briefly mention the syntax of XLOOKUP:

= XLOOKUP (search_value, search_array, return_array, [se_non_trovato], [modalità_corrispondenza], [modalità_ricerca])

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

  • lookup_value are 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 are 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 match type. The default is 0 for find an exact match . Instead, we’ll use -1, which is Find an exact match; returns the next smallest 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 updates to the XLOOKUP () function; you can refer to the columns to the left of the search column. Now, let’s get down to business.

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 the two references to the array are absolute; it’s important. If you convert the lookup table to a Table object, your references will look like this:

= XLOOKUP (C3, Table2[[#Tutti],[Basso]]Table2[[#Tutti],[Per]],, – 1)

#ALL references can be removed:

= XLOOKUP (C3, Table2[Basso]Table2[Per]],, – 1)

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

Figure B


The XLOOKUP () function returns the commission percentage.

The XLOOKUP () function returns the 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 breeze:

  1. Enter the expression = C3 * F3 into G3 and copy to the remaining cells in column G.
  2. Enter the expression = G3 in H3. This will return the first commission value in the dataset.
  3. Enter the expression = H3 + G4 in cell H4. Doing so will add the first commission to the second. Copy this simple expression into the remaining cells in column H to create a running total for the fees.

Figure C shows the completed sheet. The commission and subtotal columns depend on the percentage commission value returned by XLOOKUP (). You could return high values ​​just as easily, but you just need a lookup column. Including both is great for documentation or sharing with users, but both are not required to get results.

Figure C


The completed sheet contains the result values ​​to the left of the lookup values.

As commission percentages change, you can quickly update benchmarks (low, high, and percentages). There is no need to change functions and expressions. Everything still works.

If you haven’t upgraded to Microsoft 365, you need to use VLOOKUP (). The good news is it’s not difficult anymore. If you have Microsoft 365, I recommend using XLOOKUP (), but Microsoft is not likely to deprecate 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 cannot handle a result value that is to the left of the lookup value, but this is the arrangement we have in the lookup table. (I did this on purpose to emphasize a big difference between the two functions.) Using VLOOKUP (), you need to shift the percentage values ​​to the right of the low and high values, as shown in the Figure D .

Figure D


Move the percentages to the right of the search values.

The syntax of the VLOOKUP () function

VLOOKUP (search_value, search_array, column_index, [ricerca_intervallo])

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 the Figure E , that the function returns the same percentage values. The formulas for columns G and H are the same as before:

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

Figure E


The old search function returns the same results.

The sheet is shown in Figure F . The TRUE argument finds the closest match, which means the dataset does not need to be sorted. Also, we don’t need both low and high values ​​to get results.

Figure F


The sheet completed with VLOOKUP () is the same as the sheet with XLOOKUP (), except for the placement of percentage values ​​in the lookup table.

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

Similar Posts

Leave a Reply

Your email address will not be published.