VLOOKUP in Coda
The VLOOKUP formula in Excel is a powerful and widely used function that is particularly useful for tasks such as merging data from different sheets, generating reports, performing data validation, and creating dynamic dashboards. For example, we used it below to quickly calculate the total taxed amount based on the location of each employee. 
However, you might have noticed that Coda's formula language doesn't include VLOOKUP 🤯 Fear not! In this guide will show you how you can achieve this same functionality in Coda through relation columns or formulas.
In Coda, your relations don't have to be strictly vertical or to the right like in Excel. Instead, they will always fetch data from the entire row, starting with your display column. After the relation column is created, create a new column for the tax rate. Use the thisRow formula and the relation column to display the tax rate.
As your datasets grow, the relation column connection allows for your calculations to automatically adjust and reflect changes, saving you time and effort in keeping everything up to date!
Note: While this isn't our usual approach to formulas in Coda, we're using this method for a direct comparison in this specific case 😉 For more on the Coda formula language, check out this resource.
The Filter() formula is by far the most useful formula in the Coda formula language and can be used in countless ways. Let's use it to map the tax rates to the correct employees. Lets take a closer look at our filter() formula.
First, create a column for your tax rate. Then, add the filter formula to that column. Our approach will be to filter down the tax rate table to show the rows that share the same city name as the employee. Lastly, we instruct the formula to display the tax rate column.
VLOOKUP used in excel to map city tax rated to employees
What you'll learn:
- How to use relation columns instead of VLOOKUP.
- Formulas you can use instead of VLOOKUP.
What you'll need:
- Tables
- Relation columns
Method 1: Use a relation column
First, bring your datasets over into Coda, and name each table something descriptive. You can either copy/paste your data into Coda, or import a CSV file. Then, create a relation column connection between the two tables.A relation column is created to map the city tax table to the employee table.
In Coda, your relations don't have to be strictly vertical or to the right like in Excel. Instead, they will always fetch data from the entire row, starting with your display column. After the relation column is created, create a new column for the tax rate. Use the thisRow formula and the relation column to display the tax rate.
thisrow.Location.Tax Rate As your datasets grow, the relation column connection allows for your calculations to automatically adjust and reflect changes, saving you time and effort in keeping everything up to date!
Method 2: Use a formula
Let's say your city column is a text field, rather than a relation column. You can instead use the Filter() formula to map the appropriate tax value over. To make things easier to digest, we've dissected the anatomy of a VLOOKUP below. We've assigned numbers to each part of VLOOKUP, and we'll show you where each element appears in the Coda formula.The VLOOKUP formula in Excel
The Filter() formula
The Filter() formula is by far the most useful formula in the Coda formula language and can be used in countless ways. Let's use it to map the tax rates to the correct employees. Lets take a closer look at our filter() formula.First, create a column for your tax rate. Then, add the filter formula to that column. Our approach will be to filter down the tax rate table to show the rows that share the same city name as the employee. Lastly, we instruct the formula to display the tax rate column.
The Lookup() formula
While Codans tend to favor the filter formula, it isn't the only formula you can use to achieve VLOOKUP functionality. Coda's Lookup() formula can also link your tables.Was this helpful?
YesNo