In this worksheet, the table in B5:C9 contains quantity-based discounts. As the quantity increases, the discount also increases. Let’s set up the XLOOKUP function to calculate the correct discount for each quantity shown in E5:E11. Now, to make entering the formula a bit easier, I’m going to start by creating two named ranges. I’ll name B5:B9 quantity and I’ll name C5:C9 discount. This is an optional step, but it will eliminate the need to use absolute references inside XLOOKUP. An Excel Table would also work well in this case. Next, I’ll put the cursor in F5. After typing an equals sign and the letters “xl” I’ll press TAB to complete. The first argument for XLOOKUP is lookup_value. We want to pick up this value from cell E5. The next argument is lookup_array. For this, we want to use the named range quantity in column B. Next, we have return_array. Here, we want to use the named range discount in column C. Now, these are the only required arguments for XLOOKUP. If I stop here and copy the formula down, we get mostly #NA errors. This is because XLOOKUP performs an exact match by default. XLOOKUP returns 25% for 100, because 100 appears in the lookup array, so it’s an exact match. The other values do not appear in the lookup array. What we need in this case is approximate match, and for that, we need to enter more arguments. The next argument is the not_found message. I’m going to leave this completely empty in this case, because we don’t need it for this example. Next, we have match_mode. This is the argument we need for approximate match. You can see we have two basic options: In this case, we want “next smaller,” so I’ll enter -1. We don’t need to provide search_mode, since XLOOKUP will start at the top by default. Now when I enter the formula, you can see we have more sensible results. For each quantity, we get the correct discount. As before, 100 is an exact match and returns 25%. With a quantity of 350, XLOOKUP scans forward then drops back to the next smallest item, 300, and returns 35%.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
