Re: Formula that's working in Google Sheets, isn't working in AirTable

Andrew_Pyle · ‎May 20, 2021

Hi folks,

I’m in the process of moving some data from Google Sheets into Airtable.

In the Google sheets I have three tabs.

The first two have data added manually. The third tab uses formula to pull through the data from tabs 1 & 2.

This formula is working fine in Google sheets, but I get an error message in AirTable when trying to switch the column type to formula.

I’m sure it’s something simple, like I’m referencing the wrong fields in the formula, but as I’m a newbie to AirTable, I’m struggling to find a solution.

Justin_Barrett · ‎May 20, 2021

Welcome to the community, @Andrew_Pyle! :grinning_face_with_big_eyes: Unfortunately you can’t do a straight copy of a working formula from a spreadsheet and expect it to work in Airtable. Airtable is designed more like a database than a spreadsheet, and many of the elements in that formula don’t exist in Airtable’s formula syntax:

ARRAYFORMULA
VLOOKUP
ROW
OFFSET
INDIRECT

Airtable formulas also don’t support arbitrary row/cell references (e.g. B4), or references to data in other tables (e.g. Widgets!B2:B). Also, because an Airtable formula field may only contain a formula, there’s no need to start it with an equals symbol.

I suggest spending some time reviewing other formula discussions here in the forum, and also reading through the formula field reference to understand what functions are available:

What you’re trying to achieve may be possible through a combination of design changes—you’ll need to add link fields to access data from other tables using rollup/lookup fields—and formula refactoring, but you’ll need to spend some time learning the key differences between spreadsheets and Airtable before we can dig into the nitty-gritty of what you want to accomplish.

See Solution in Thread

Justin_Barrett · ‎May 20, 2021

Welcome to the community, @Andrew_Pyle! :grinning_face_with_big_eyes: Unfortunately you can’t do a straight copy of a working formula from a spreadsheet and expect it to work in Airtable. Airtable is designed more like a database than a spreadsheet, and many of the elements in that formula don’t exist in Airtable’s formula syntax:

ARRAYFORMULA
VLOOKUP
ROW
OFFSET
INDIRECT

Airtable formulas also don’t support arbitrary row/cell references (e.g. B4), or references to data in other tables (e.g. Widgets!B2:B). Also, because an Airtable formula field may only contain a formula, there’s no need to start it with an equals symbol.

I suggest spending some time reviewing other formula discussions here in the forum, and also reading through the formula field reference to understand what functions are available:

What you’re trying to achieve may be possible through a combination of design changes—you’ll need to add link fields to access data from other tables using rollup/lookup fields—and formula refactoring, but you’ll need to spend some time learning the key differences between spreadsheets and Airtable before we can dig into the nitty-gritty of what you want to accomplish.

Andrew_Pyle · ‎May 21, 2021

Thanks @Justin_Barrett, appreciate the clarification.

I’ll dig into the support material to learn more.