How to VLOOKUP and Merge Tables in Alteryx

Alteryx is a popular tool for accountants and people who need to automate Excel processes. While there’s no direct replacement for a VLOOKUP in Alteryx, people can use the Join tool to merge and combine data from two different sheets or datasets.

We’ll explain the pre-requisites for joining data in Alteryx to avoid errors when re-creating a VLOOKUP in Alteryx and explain some tips and tricks to making to make it easier. The first thing to be aware of that’s different from Excel is the concept of data types.

Let’s jump in!

Understanding Alteryx Data Types

The reason we want to explain Alteryx data types up front is because it’s the most common reason that a VLOOKUP or Join fails in Alteryx. Each column in a data set that is imported into Alteryx is assigned a data type, there are a number of variations, but the basic ones are numbers, strings (text) and dates.

When combining two separate data sets in Alteryx, there has to be at least one column that has matching data between the two so Alteryx knows which values to match. These columns have to be the same data type, number, string (text) or date prior to going into a Join tool to combine them or the operation will result in an error when you go to run the workflow.

How to VLOOKUP Data in Alteryx

To bring in a column of data from a data set and combine it with another, use the Join tool from the Join section of the Alteryx Toolbar. Connect the primary dataset to the Left (L) input, and the target data set into the Right (R) input. Then configure the tool by telling it the columns from each side that contains a common value.

By default the Join tool will bring in all columns from the left and right table. You can select to only bring in the one column that you need.

Here’s how in more detail.

First bring in both sets of data into your workflow. Then bring in a Join Tool from the Join section.

The Join tool is available in the Join section of the Alteryx Toolbar

The Alteryx Join tool has 2 inputs, Left and Right with 3 outputs, Left, Join and Right.

Close-up of a Join tool with two inputs and 3 outputs, L, J, R

The starting data that you want to use a VLOOKUP on, goes into the Left (L) side of the Join tool. The value you want to find with a VLOOKUP goes to the Right (R) side of the join tool.

In the screenshot below, we have a dataset with a SKU # and Sales Amounts that is connecting to the Left side of a Join tool. Then on the Right side of the join is a SKU # and an Item Name.

The SKU field is the common value between the two.

Example of configuring a Join tool to combine data based on a common value like an Excel VLOOKUP

After connecting the two datasets into the Join tool, you need to configure it on the left configuration panel, which involves two steps.

1.) Choose matching columns from the drop down menus at the top of the page. This is the column, or columns that the two datasets have in common. You can add multiple columns of criteria by using the second row of dropdown menus at the top. To delete them use the – symbol with a circle around it to the right of a dropdown.

2.) Setup the embedded Select tool. The options here are the same as in a Select tool. You can drop re-arrange and re-name columns.

By default, Alteryx will show all columns from the left and right dataset unless you unselect the ones you don’t want to move forward with in your workflow.

The 3 Output Types of an Alteryx Join Tool

You’ll notice that there are 3 outputs of a Join tool. Left, Join and Right. These allow you to toggle between seeing which records matched, the (J) Output and which records were in the left data set but not the right (L) and vice-a-versa.

When you click on the J output, you will only see the records that matched. In the example below the left input had 5 rows of data, and the right input only had 3 records. The Join output shown in the workflow results below is a total of 3 records.

The Join output showing matching values between two data sets

Using the same example, we can toggle to the Left output which will show the 2 records that are in the left input that are not in the right input.

A Join tool Left output showing only values that matched the left side of a join

You can also toggle between the L, J, and R outputs by using the 3 buttons at the bottom right of the workflow results page.

How to Left Join in Alteryx

To perform a Left Join in Alteryx, or one where you have all records from the left and matching records from the right, use a Join tool to connect the left and right data sets. Then add a Union tool to the right of the Join, and connect both the L and the J outputs of the Join tool.

Example of using a Join and Union tool to create a Left Join

When there are values that are in one data set but not the other, Alteryx will return the values as null.

How to Full Outer Join in Alteryx

To perform a Full Outer Join in Alteryx, or one where you have all records from the left and all records from the right, use a Join tool to connect the left and right data sets. Then add a Union tool to the right of the Join, and connect both the L, J, and R outputs of the Join tool.

Example of using a Join and Union tool to create a Full OuterJoin

When there are values that are in one data set but not the other, Alteryx will return the values as null.

Alteryx Join Data Types Don’t Match Error Message

The most common reason that a Join will fail is because data types of the common columns in a Join tool are not the same data type. To correct the error, add a select tool prior to one of the inputs going into a join tool to match the data type with the other side of the input.

For example, the following workflow resulted in an error that “String fields can only be joined to other string fields”

String fields can only be joined to other string fields Alteryx error message

A quick way to identify the issue is to click on the configuration of the Join tool as it will show you the data types of all of the columns in both data sets and the matching criteria being used. As you can see below, the left SKU field is a V-WString (text) data type, and the Right SKU column is an int32 (numeric) data type.

identifying the columns with a data type mismatch in an Alteryx Join

Important: You cannot fix a data type mismatch in a join tool by changing the data type in the configuration panel of the Join tool. These settings only affect the J Output of the workflow, the data type has to be changed before it goes into the Join tool to begin with.

To change the data type of a column, add a Select Tool from the Preparation section before the input of a Join tool and change the column data type from the dropdown menu.

The setup will look something like this:

How to correct a data type mismatch Join error in Alteryx with a Select tool

It doesn’t matter which side you change, left or right. Often times it is determined by the nature of your data and what has to happen to it downstream. However, for the sake of the Join the two data types just have to match.

In our example, we updated the Left input, but could have just as easily updated the right input.

Conclusion

While the VLOOKUP function doesn’t exist in Alteryx like it does in Excel, you can use a Join tool to combine two data sets based on a common value. Connect one data set to the Left input of a Join tool, and the other to the Right input of a Join tool. Then configure the Join tool by setting the common column or columns, and de-selecting columns you don’t want to move forward with in your workflow.

To keep all columns on the left, use a Union tool to append the Left and Join outputs as the Join output will only show you the values where matches occurred. You can also use a Union tool to combine the Left, Join and Right Outputs to include all records from both sets of data.

Scroll to Top