Lookup

[Steve]

There are many circumstances where you want to bring two (or more) datasets together. There are two directions that you can merge datasets. If you have the same kind of data, like January Sales and February Sales, you will probably want to append them to each other and make a longer list of data. If you have data where one dataset helps to describe the other dataset, like the products hierarchy data that helps to describe the transactions data, you will most likely want to do a lookup between the two.

In this article we will focus on the lookup capability. In a future article, we will go in more detail between a lookup and a join. While the two seem similar, there are some significant differences.

Excel

Excel performs a lookup by using the vlookup() function. It is essentially an advanced search function that finds a cell in a table of data. For this to work, you will want to have both the main table and the lookup table on separate tabs in the same spreadsheet. Because of the complexity, I tend to create one lookup column in the main table at a time even if there are multiple items in the lookup table that I want in the main table. If you already know how to build a vlookup(), you probably know that the first time can be difficult. That is probably why there are so many websites and videos that try to help make it easier.

Set up the lookup table

If you don't already have the lookup table in your spreadsheet, add it on a separate tab.
Move the values to match to the first column. If you want to match on multiple columns, you will have to concatenate the columns together because the vlookup() function only supports a single column to search. If you don't already know the appropriate matching column, you may want to ask the person that provided the data to you, otherwise you will need to manually look through both tables to figure it out.
Sort the table on the matching values

Perform the lookup

Insert a blank column where you want data from the lookup table to show up in the primary table

Give it a column name
Type in the vlookup() function
Ensure that your anchors are correct
Copy/Paste the vlookup() to all the other rows in that column

Repeat this process for each of the columns that you want to add to the main table

Paxata

Click on Attach, then select Lookup



Click on the "dataset (click to select)" button




Click the "SELECT" button next to the dataset that you want to use as a lookup




If you know which columns are used to bring together to two datasets feel free to select them from the left and right sides




If you don't know how the data comes together, click on the "DETECT JOINS" button and Paxata will run an advance algorithm that will look at the data and provide suggestions. It will automatically select the pattern with the highest score.




Click Save


Using these setting will give you the same results as an Excel vlookup(). There are many additional options for controlling how the lookup works and if you need help identifying them, just ask us below!