We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Replicate Excel XLOOKUP Function in Alteryx Designer

Alteryx Community Team
Alteryx Community Team

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.

Look up employee information by employee ID number

employee lookup.jpg

The basic XLOOKUP functionality is included with the Filter tool...


Find next largest item


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.


Sum values between range


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.

No ratings
7 - Meteor

I have another use case and hope to get more tips on replicating XLOOKUP on Alteryx:

How to apply XLOOKUP in Alteryx - Alteryx Community