09-09-2019 03:11 PM - edited 08-03-2021 10:59 AM
Excel has a new function called XLOOKUP. It's still in beta so you may not have access to it yet. The help page includes several examples of how to use it. The attached workflow contains snippets that show how to accomplish these tasks in Designer. Three examples are explained below.
The basic XLOOKUP functionality is included with the Filter tool...
With the match_mode argument set to 1, XLOOKUP will look for an exact match, but if it can't find one will return the next larger item. To do this in Designer, we'll again start with the filter tool to narrow down the tax rates to those above the income threshold, then we'll sort by max income and choose the first record.
In this example we're choosing to sum the total prices of the products between Grape and Banana inclusive. The Excel function is a bit complicated here, with a sum and two xlookup functions. If there were a numeric order or record id column we could do this pretty easily with one or two Filter tools, but as is with the arbitrarily ordered product categories we'll use a Multi-Row Formula tool to decide which rows to include in our sum calculation.
There are other ways to accomplish XLOOKUP functionality in Designer. Feel free to suggest your favorite in the comments below.
I have another use case and hope to get more tips on replicating XLOOKUP on Alteryx: