Joining data together in spreadsheet software is done via the VLOOKUP function. The "V" stands for vertical lookup, which means the lookup happens vertically. Examples always help in times like this, so let's dive in.
Let's join the Getting Started: Users dataset to the Getting Started: Payments dataset. Any hypothesis as to what our linking field is?
If you arrived at User ID, then 👍🏼, as that's exactly right. User ID is the architected link between our two datasets, which means we can use that as our foundation for joining them together.
Now to our example setup, let's join our two datasets together to pull the Payment Amount for all users that have made a payment so that we can then analyze that data alongside the fields in our Users dataset (e.g. Payment Amount by Acquisition Source, Country, etc.).
Here's what this example would like like broken down by the parameters needed in our VLOOKUP function:
|Search Key||What cell value are you looking to match to another cell value?||
|Range||Which cells do you want to include in your search, starting with where we'll find a match to the Search Key||Entire Payments dataset
|Index||Starting at 1, how many columns over is the cell value we want to extract where we find a match?||Payment Amount|
Does it have to be a perfect match?
|Exact Match on User ID|
And now let's move over to Google Sheets. For this example, we created one tab that contains both the Getting Started: Users dataset, as well as the Getting Started: Payments dataset. We simply copy and pasted one over to the other (we're hiding Columns C-F in the example below, and thus only showing the User ID field from the Getting Started: Users dataset).
Here's what our example table above would look like live in Google Sheets (use VLOOKUP to search by User ID from Users to Payments, and when there's a match, surface the Payment Amount for that user):
In the Formula Bar you'll see the parameters that are set, =VLOOKUP(
- Search Key (B4) - for the User ID in our Getting Started: Users dataset
- Range (J4:N4) - scan this selection of cells in our Getting Started: Payments dataset
- Index (4) - if there's a match, pull back the cell value from the 4th field in our Range
- Is Sorted (FALSE) - yes, we want an exact match on the User ID
Now, to setup our VLOOKUP formula so that we can then drag our formula down and perform this lookup for all users, we need to make one update. And that is to fix our Range. As in, when we drag our formula down we need the Range to stay still. This is known as Absolute Referencing, and we use the $ symbol to tell Google Sheets which Column:Row we want to fix:
We want the entire range to stay still so we Absolute Reference the Column and Row for the entire Range ($J$4:$N$28), which allows us to then drag our formula down and get the following result (when a User ID is not found Google Sheets will return an Error Value of "#N/A"):