Lookup or Rollup with formula

[Michael_Novotny]

I want to perform a vlookup into a separate table. Similar to in excel, you can vlookup from another tab.

However, it seems the only way to do that is by using the lookup field. That is pretty straightforward, however the field with the linked records must be a text field. I’d like to have that be a formula.

Here is an example:

1. Create formula that combines two columns.
2. This creates a unique value
3. I want to use that unique value (that uses the formula) as the value to lookup into another table to pull in a value from that table.

Is this possible? I just want to execute what a simple vlookup would do in excel, but by using a formula instead of single text to be the value that lookups into the second table.

[Justin_Barrett]

Welcome to the community, Michael! :grinning_face_with_big_eyes: Unfortunately what you describe isn’t possible. Lookups are tied to link fields, and those link fields aren’t just text. They’re a pointer to a specific record in another table, and those pointers cannot (currently) be made via formulas, though it would be kinda magical if it were possible.

Can you describe in more detail what you want to achieve, using specific examples from the base you’re creating? What you want may still be possible, though it might take a different combination of tools than what you may initially think.

[Michael_Novotny]

Hey thanks Justin I appreciate the insight.

Yes using my base as an example this is my situation:

1. I have a zapier inserting a new row for entries submitted from a form.
2. I then want to take two of the columns submitted and join together in one column. (My assumption is using a formula to do this.
3. Then take that joined column as a lookup into another table to bring back a value.

Does that make sense?

Here is better example:

1. User submits value “a” and value “b”. Both in different columns within Airtable
2. Then in another column have a formula that joins together to make a new value: “ab”
3. Use that new value “ab” to perform a lookup into another table to bring back a corresponding value from that other table.

[Justin_Barrett]

What action are you using in Zapier to send the data to Airtable? Some systems allow you to pass in a linked record ID or the equivalent text value when filling in a link field. Let me explain…

Let’s say you’re taking orders for produce. In your form, you have one field where the user picks the type of fruit (i.e. apple), and another where they pick its size (i.e. large). In Airtable, you want to look up the price based on this type-size combo. To do this, you have an [Items] table that looks something like this:

What you’re looking to do is pass the separate type and size details into an [Orders] table inside of Airtable, combine them into a single string—i.e. Apple:Large—and use that to make a link to the appropriate record in [Items] so you can look up the price. Is that pretty much the gist of it?

If so, you might just try combining them directly inside of Zapier, and passing that combo into the link field. Like I said above, some actions will let you enter either a record ID or the equivalent text. In your case, you’ve got the text in two separate parts from a previous step: Apple and Large to use my previous produce example. When adding data from a previous step, an item is rendered in Zapier as a capsule with text inside it, something like

(Step 1|Apple)

Pardon my makeshift Zapier capsule drawing :winking_face:

What I’m suggesting is to put both parts, plus any separator you’re using in Airtable, into the Zapier field that will feed your link field in Airtable. In the end, it would look something like this in Zapier:

(Step 1|Apple):(Step 1|Large)

When fed into the link field in Airtable, it would become Apple:Large and match up with the appropriate record from [Items], allowing you to look up the price.

[Michael_Novotny]

Hey Justin this is brilliant. I had not thought of that. This concept makes good sense. Let me go try that and let you know. Thank you!