Alteryx Designer Desktop Knowledge Base
Definitive answers from Designer Desktop experts.Alteryx for Excel Users: How to do a VLOOKUP in Alteryx
VLOOKUP is one of the most commonly used functions is Excel. VLOOKUP takes a lookup value and finds that value in the first column of a lookup range. Complete the function's syntax by specifying the column number to return from the range. In other words, VLOOKUP is a join. One column of data is joined to a specified range to return a set of values from that range.
The objective of this posting is twofold:
1. Demonstrate how a VLOOKUP can be done using Alteryx.
2. Demonstrate how a VLOOKUP can actually be easier to do in Alteryx.
For the sake of argument, let's say we have two worksheets in one Excel file. Sheet 1 contains a master list of sales data. The data looks like this:
Sheet 2 contains a select list of sales people and contains First Name and Last Name:
Using a VLOOKUP function, we want to take the list of sales people, match it to sales peoplefound in Sheet 1 and return sales data for each sales person. Notice our list of sales people in Sheet 2 has two sales people named'Angelina'. Sheet 1 has multiple people named'Angelina' and 'Michael'. Using First Name alone will not return reliable results using VLOOKUP. Further, matching on Last Name - or even a concatenation of both First Name and Last Name - isn't always reliable either. There are a lot of 'Michael Thomas's in the world and several may exist in your data range. The best way to complete our VLOOKUP is to use a unique identifier for each sales person. But again, for the sake of argument, let's say each First Name and Last Name combination produces a unique name/identifier.
The first thing we need to do is do is concatenate First Name and Last Name in a new field.
We'll do the same thing with our data in Sheet 2. For VLOOKUPs to work properly, the data must be sorted. In our case, we'll sort both Sheet 1 and Sheet 2 in ascending order on 'Concatenated Name':
Now we're ready to use our =VLOOKUP function:
It worked! Let's see how do the same thing in Alteryx. (Hint: there's more than just one way to do it).
We'll start by bringing in our data from Sheets 1 and 2:
As with the Excel example, we'll concatenate First Name and Last Name in a new field. For clarity, let's name the concatenated name in Sheet 1 'Full Name' and Sheet 2 'New Name'. We'll do this using a Formula Tool expression:
Finally, let's attach a Find Replace Tool where Sheet 2 attaches to the 'F' input of Find Replace and Sheet 1 attaches to the 'R'.
The configuration for the Find Replace looks like this:
Notice 'Sales' is selected under 'Append Field(s) to Record'. When we run the workflow, we get the sales data by sales person just like we did in Excel:
But there's an even simpler way to perform a VLOOKUP in Alteryx! Simply join the two Sheets with a Join Tool.
Take a look at how the Join is configured:
We joined our data on First Name andLast Name without having to concatenate the two fields first. Further, we selected the sales data we want returned in the same Join Tool.
Alteryx has an added advantage over Excel's VLOOKUP function. Let's say you want to return Sales and Opportunities. With Alteryx, this can be handled in a single Join. With Excel, we would need to do multiple VLOOKUPs.
Virender,
This could be done in various ways. I have used the Find & Replace tool. PFB the screenshot.
Hope this helps.
Best,
Raghav
