LOOKUP function for fetching values from linked records

[Andy_Lin1]

I'd like to see a formula function for looking up values in linked records, something like:

LOOKUP({Linked Record field}, {Field in linked table})

Basically, a two-criteria lookup similar to "INDEX MATCH MATCH" in Excel without needing to import all the fields from the linked table into the current one. Or just a dot/membership operator in SQL databases.

Currently, if I want to fetch data from a linked record, I need to use a Lookup field. This is fine if I want to display  the field's values in my table or consistently refer to it in a formula; but if I have multiple fields I want to reference in a single formula, then I would need to add a Lookup field for each of the source fields. This adds an number of extra fields to my base that only serve a single formula field and clutter up my field lists. (Or I would need to do something hacky with the formula, or use scripts/automations.)

For example, say I have two tables: Articles and Authors. Articles has fields like Article Author, Article Type, and Article Cost. Authors has fields like Name, Email, and the rate schedule (News Rate, Feature Rate, Review Rate), as below:

• Articles
• Article Author – Linked record: Authors
• Article Type – Single select: News, Feature, Review
• Article Cost – Formula: ?
• Authors
• Name – Short Text
• Email – Email
• News Rate – Currency
• Feature Rate – Currency
• Review Rate – Currency
• Articles Written – Linked record: Articles

If I want to lookup an author's email address, I can add a Lookup field and it's a good reference to have for every record in my article table. However, for an article's cost, I only want to see the relevant rate from the author's record. Currently, in order to do this, I'd need to add Lookup fields for each rate because the Formula field can only reference fields in the current table:

• Articles
• Article Author – Linked record: Authors
• Article Type – Single select: News, Feature, Review
• Article Cost – Formula: SWITCH({Article Type}, "News", {News Cost}...
• News Cost – Lookup: Author.{News Rate}
• Feature Cost – Lookup: Author.{Feature Rate}
• Review Cost – Lookup: Author.{Review Rate}

But now I have three fields dedicated to this one formula. The fields are of no other use in that particular table and are basically clutter that I have to hide in every view. If a LOOKUP function existed, then I don't need those extra fields, and instead my formula would look something like

SWITCH({Article Type}, "News", LOOKUP({Article Author}, {News Rate})...

This would benefit anyone working with rate cards, inventories, or schedules (or any case where they need to perform a variable-field lookup).

Another use case is doing a "deep" lookup, such as getting the country code for a phone number based on a city. Say you have a base with tables Phone Numbers, Cities, Countries; and Phone Numbers are linked to Cities, and Cities are linked to Countries; and {Country Code} is a field within Countries. Because Lookup fields in Airtable only go to one other table (you can't lookup a Linked Record field in another table and get data from that third table), you would have to add a Lookup field to Cities for {Country Code}.

[claes]

This would solve so much cluttering and headaches in my bases, please make this happen!

[kasperdam]

Quite surprised about the lack of this, which seems like a common feature with competing solutions. 🥲

[PeterFontana]

Why is this not standard... I can do this with Google Sheets, seems like a no brainer that this should be in AirTable.

I am trying to simply have a list of accounts with account types and return the correct product commission for each account type from the product commission table. But the product commission is based on the total value of the account. This is a pretty simple lookup formula in google sheets or excel that seems impossible with AirTable without unnecessarily complex scripting.

[kasperdam]

Causes a crazy amount of helper columns. 😞