Jul 20, 2019 09:37 AM
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:
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.
Jul 20, 2019 09:51 AM
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.
Jul 20, 2019 10:20 AM
Hey thanks Justin I appreciate the insight.
Yes using my base as an example this is my situation:
Does that make sense?
Here is better example:
Jul 20, 2019 11:55 AM
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:
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
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.
Jul 20, 2019 07:13 PM
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!