community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Replicate Excel XLOOKUP Function in Alteryx Designer

Sr. Community Content Manager
Sr. Community Content Manager
Created on

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

 

formula.png

 

Find next largest item

 

1e91c8d7-6130-4936-a8f5-018b15403898.jpg

 

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.

 

sort.PNG

 

Sum values between range

 

1215cf29-7cad-42b8-96b8-5416376d33de.jpg

 

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.

 

multi.PNG

 

There are other ways to accomplish XLOOKUP functionality in Designer. Feel free to suggest your favorite in the comments below.

Attachments