I have two tables, Table 1 and Table 2, with different fields. When a specific record is populated in Table 2, I want the values of some of its fields to automatically appear in a corresponding record in Table 1where the cells were initially empty, based on the primary fields of both tables, I’m calculating a total in Table 1 based on entries into records in several tables and need to have the total in Table1 automatically updated whenever the values change in the other tables. What is the best way to achieve this? IOW, what is the equivalent of XLOOKUP or Index Match in Airtable?
Solved
XLOOKUP and INDEX Match Equivalent in Airtable
+3Best answer by TheTimeSavingCo
Hmm, from your screenshot, I’d first recommend consolidating the data; instead of having two tables where you have items and values, it should all be in a single table
After that, I’d recommend creating a new table where each record represents a single grouping of your items, and then linking your items to that grouping record, and then you’d use a rollup like in the previous example
---
With your current data structure, you’d need to:
- Create a linked field to Table 2
- Link the records together
- Create a lookup field to pull the value over
- Create a formula field to consolidate the values
- View the total sum like so:
-
This is fine if you’re doing something quick and dirty but generally not recommended, and you can find it set up here
+31You’d generally accomplish this via linked records and rollup fields:
How we set up the links between the records is going to depend heavily on your workflow though, and it could either be done manually upfront, or accomplished via an automation. If you could provide some screenshots of your tables and talk a bit about what your workflow is that’d be helpful!
+3Thank you so much! I need the 500 to appear in Table 1, and the total to be calculated in the total row/record
+21To solve for this, you create a linked record field between the two tables, and then add a lookup field in table 1 that displays the Value 2 field (which can be done while you’re adding the linked record field tbh). You’ll then have both a Value Field and a Value2 field from Table 2. Finally you add a formula field that computes the numbers, basically “ {Value} + {Value2} “ and call that field “Total”.
I might suggest making those Value fields either number or currency fields instead of long text to ensure nothing goes wonky later, but just tested this and it works fine as is.
+3Thank you for your help. Being new to Airtable, I'm still learning the basics so I appreciate your patience with my questions. Regarding the total row, do you mean that I can’t have a total row/record that calculates the sum of a specific set of values? and that it needs to be implemented as a separate field or column?
+21Nope, rows aren’t blank spaces to be filled in ala an Excel sheet. Airtable does include some basic calculations at the bottom of grid views that can be used to sum and average single columns, but the rule of thumb is that information moves horizontally instead of vertically.
For quick usage, you can use those grid view calculations along with group or filtering to do basic calculations (though your field needs to be a number/currency type).Longer term, to calculate a sum of a set of records they should be linked to an overall record in another table which then can rollup and sum those values.
Airtable is database software that looks like a spreadsheet, not just a spreadsheet alternative
+31Hmm, from your screenshot, I’d first recommend consolidating the data; instead of having two tables where you have items and values, it should all be in a single table
After that, I’d recommend creating a new table where each record represents a single grouping of your items, and then linking your items to that grouping record, and then you’d use a rollup like in the previous example
---
With your current data structure, you’d need to:
- Create a linked field to Table 2
- Link the records together
- Create a lookup field to pull the value over
- Create a formula field to consolidate the values
- View the total sum like so:
-
This is fine if you’re doing something quick and dirty but generally not recommended, and you can find it set up here
+35
Unlike a spreadsheet, Airtable can only summarize data from records that have been linked through linked record fields.
The only 2 exceptions to this are:
- The summary bar, which shows up in grid views above groups and at the bottom of the screen.
- The number field in interfaces.
Also, since you’re new to Airtable, you might benefit from my free Airtable training course, which you can take for free by signing up for a free 30-day trial with LinkedIn Learning.
!My course is outdated because it was created with an old version of Airtable in 2020, but the core concepts still remain the same, such as linking records across tables and using lookup fields.
Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.