Datasets | ✅ Free | ✅ Basic | ✅ Pro | ✅ Business |
VLOOKUP | ❌ Free | ✅ Basic | ✅ Pro | ✅ Business |
When working with large amounts of data, it can be difficult to find specific information. This is where the VLOOKUP function comes in handy.
VLOOKUP, short for "vertical lookup," is a calculator function that allows you to search for a specific value in a column of data and return a corresponding value from another column. In simpler terms, it helps you find and output specific information from a dataset. You can also add VLOOKUP inside functions for more complex formulas.
Prerequisites
In order to use the VLOOKUP function, you will need to have a basic understanding of how to use it. We suggest that you go through the following help article in order better familiarize yourself with it: Use functions & formulas > VLOOKUP
Once you are comfortable with basic formulas, you can start using VLOOKUP to output calculator results. Let's take a look at how to do this.
Adding Your Datasets
Before you can use VLOOKUP in your calculator formulas, you'll need to add a dataset that has the data you want to look up.
The first column should contain the values you want to search for, and the second column should contain the corresponding values you want to output in the calculation. For example, if you are using a calculator to calculate the area of different shapes, your first column could contain the shape names (circle, square, triangle) and the second column could contain the corresponding area values.
In order to add a dataset, just head over to the gear icon in the editor, and pick the "Datasets" option.
Then you'll just need to enter the dataset's name and its URL.
If you make any changes to your dataset and need to resync it, simply click the "Sync" button, and to delete it just click the X button that appears on the top right side of the dataset (hover the cursor over the dataset and the X will appear).
ℹ️ Make sure you enter your dataset's name correctly, as it will not be possible to rename it once it has been added. If you need to change the name however, you can always remove the dataset and add it again.
You can also add and manage datasets inside of the calculator's formula builder.
To add a dataset click the "+ Add Dataset" button, you can then sync the dataset by clicking the counterclockwise arrows icon 🔄, and to delete the dataset simply click the trash can icon.
Once you've added a dataset, it will be available organization-wide. This means that any funnels you create, no matter the workspace they're in, will all have access to the datasets you've added.
Using VLOOKUP
Once you've added a dataset, you'll be able to use the VLOOKUP function in your calculations.
The way this function works, is by searching for the answer that the participant picked, in column 1 of the dataset, and then returning the value in column 2 (index=2), or column 3, and so on, of the same row.
As an example, let say that this is our data set:
And this is the syntax for our calculation formula:
What the formula will do in this scenario, is check if the answer that the participant picked in Q1, matches any of the options in the dataset's (DS1) first column (column A). If the answer matches any of the options, the formula will check the second column (index=2) for the value that it should return.
With this being said, if the participant picks the answer "Apple" then the calculation will output a value of 11, if they pick the answer "Banana" the calculation will output a value of 15, and so on.
You can use the VLOOKUP function on its own, or inside of a formula (nesting) for more complex calculations.
Making a global formula out of a VLOOKUP formula is also a good way to use VLOOKUP inside another formula, this allows you to keep the syntax short and easy to manage, as opposed to having a long and complex looking formula.
Read more about global formulas here: Calculator: Global Formulas
Important
When adding your dataset, make sure you use a public Google Sheet URL.
You can add a maximum of 10 datasets.
Datasets can be resynced every 5 minutes, this is done manually.
The import will ignore rows where the first column is an empty cell! (this is because VLOOKUP can only search in the first column).
Any datasets that you add will be available globally across your organization, this means that all your workspaces and funnels inside the organization will have access to the datasets.
Use Case Examples
Different Price Rate Based on the Amount of Ordered Units
Calculator formula: =VLOOKUP(Q2,DS2,3)*Q3
Where Q2 is the amount of units the participant wants to order, DS2 is our database, 3 is the index (the column of the dataset that has the prices), and Q3 is how often should that amount of units be delivered per month.
In the above example, we're using VLOOKUP to find the price based on the amount of units that are being ordered, and then that amount will be multiplied by how often in a month those units should be delivered.
The VLOOKUP formula will check for the correct price on our dataset, which will depend on how many units will be ordered. For example, if less than 30 units are ordered the price will be $12, but if more than 90 units are ordered, then it will be a lower price of $8.
Other Use Cases That Use This Same Logic:
If you want to have price rules based on the date the participant choses (where certain dates are more expensive than others) - this is quite popular with wedding photography dates for example.
Or in a real estate business, perhaps you might want to compare the participant's input against the information in your dataset in order to return a value for affordability.
IELTS Band Score Calculator
Calculator formula: =VLOOKUP(Q1,DS1,2)
Where Q1 is the score for listening, DS1 is the dataset we're using, and 2 is the index (the column that has the band score).
In the above example, the participant's raw score in Q1 will be checked against the first column of our dataset which has all the possible scores, and the calculator will then return the value in the second column (index=2) of the same row.
For the other 2 questions (General Reading and Academic Reading) we would create a calculator for each one of them, this means we would have a total of 3 calculators, where each calculator would output the score band for each respective module.
For General Reading the formula would look like: =VLOOKUP(Q2,DS1,2)
For Academic Reading the formula would look like: =VLOOKUP(Q3,DS1,2)