Lookup in formula field

[tkhk_m]

I would like to use lookup in formula field.
How can I make it?

Here is my Airtable.

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

In the Table A, the data which entered by the user via form is “a”.
Then the data will convert by a formula into “aa”.
This “aa” is the key data in the Table B.
I want to lookup the data form Table B using “aa”.

Here is the form.

Airtable

Form - Airtable

Fill out the "Form" form quickly and securely on Airtable.

Thanks for your great support!

[Elias_Gomez_Sai · Accepted solution]

I don’t know if I understand your question, but you should related both tables with a Linked Record field and use a real Lookup field.

[tkhk_m · Accepted solution]

Thanks you for your reply!
It might be needed Linked record field.
Ii would be good for me making Linked record automatically based the inputted data.
Manual selecting linked record is not good for my airtable.

[Elias_Gomez_Sai]

Could you explain deeper what you want? I don’t get it with your “aa bb” schema :grinning_face_with_sweat:

tkhk_m:

making Linked record automatically based the inputted data

I have a Events table and a Venue table linked with Linked Records. When I create a new event I fill:

• Name of event, like “Birthday Party”
• Venue name (linked record), start to fill, like “carl” for “Hotel Carlton”, it appears, I press Enter, and done with 5 strokes.

The data inputted makes the link, “automatically”.

[tkhk_m]

Thanks your support!
I have understood about the “lookup” function and I am using this function other my tables.

In this time, I would like to “lookup” the data based generated data.

It is easy to use “lookup” the data is directly input to the table.

As you know, “aa” is just a sample.

In the Table1, the inputted data is only “a”.
This “a” will covert to “aa” by formula cell.
I would like to lookup using “aa” as a key data.

Table2 has only “aa” data.

And also it will be difficult to input “aa” directly into Table1.

[William_Ayer]

@tkhk_m Thanks for sharing your database. I think I understand your question now.
You are generating the primary key for Table 2 in the “Formula” field in Table 1.

You’d like the tables to be “automatically” linked based on the foreign_key / primary_key relationship, but as far as I can tell Airtable doesn’t currently allow for lookups into other tables that Airtable doesn’t know are “linked”.

For an Airtable lookup to work (as I’m sure you know), Airtable has to know how the two tables are linked.

It would be great if an Airtable formula could be used to do arbitrary lookups in related (but unlinked) tables, but currently I don’t see any way to do that (without resorting to using their API).

Having a LOOKUP function that could pull data from other related tables without requiring them to be linked by hand.

Some thing like:

LOOKUP (sqlStatement, destinationField)

In your case:

LOOKUP (“select table_2.GetThisInformation from Table_2 where Table_1.Formula = Table_2.Name” , LookUpInformation)

AirTable certainly feels like it is backed by a relational database, but I have no idea if there is traditional SQL database under the hood. Implementing this sort of feature might be much more complex for AirTable, since they let multiple admins modify with the schema (with rollbacks) on a live multi-user system.

[tkhk_m]

@William_Ayer
Thanks for your comment!

According to your comment, it is difficult for Airtable to use lookup function “automatically”.
Is my recognition right?

In my table, I could realize to lookup “automatically” using the “Zaiper”.

Zaiper pick up the related record instead of hand selecting.