Vlookup for Airtable?

[Lisa_Altizer]

Greetings,

I have an Airtable field titled: Course Status and to the right of that field is Total Enrollment. In my Excel spreadsheet version, I used a vlookup to return the value of ‘course status’

The formula: =VLOOKUP([@Enrolled],Table30,2,FALSE)

Table30
0-2 enrolled = On Standby
3-9 enrolled = Low Enrollment
10-28 = Will Make!

Is there a way to accomplish this in Airtable?

Thanks in advance for your support!

Lisa

[Greg_F]

Hi,

You could use the new Automation feature to run a script when a record is added.

Note 1 - The scripts in Automation are limited by run time (30 sec) and CPU use (1 sec? I believe) so more complex scripts will not work.

Note 2 - Since you are talking about adding a new record, this could be actually a much simpler script, just searching for a value from the new record in the target table to match. This could work depending on size of the database. It might be also possible to use Automation action Update instead of script, but I would have to check this one.

[Judson]

Thanks, this is great! I think you need to add await to the

await mainTable.updateRecordAsync(record, {

line, I was having trouble in a slightly larger table of it just updating some records.

[Greg_F]

Yes! Thank you for noticing that! I have corrected the gist.
:pray:

[Laura_Greer]

@Greg_F This code is tremendously helpful. In your example if the value of item.barcode is removed the link in the Product field remains? In my instance I want that link to be severed. Do you know how I could do that?

[Greg_F]

@Laura_Greer you right, the link would remain if the value in the lookup table gets deleted (unless the record in the lookup table gets deleted as well). In this case, you would have to run the script again, with an explicit declaration that if there is no match - the link field should be updated to empty.

[Laura_Greer]

Yeah, I can’t figure out how to do that. I’m not. I’m a complete novice. Can you give me a push in the right direction?

[christoph]

@Greg_F Thank you so much for sharing! One question: How can I make the script to return multiple values if the Barcode is used more than once. I set the Products field up to accept multiple linked records but the script just returns one. Thanks in advance. Christoph

[openside]

For a No-Code approach to Vlookups in Airtable, we have a function for that in our On2Air: Actions product that can be set up in minutes:

On2Air Support

Function - Lookup Values

Function - Lookup Values

On2Air | Accomplish Anything with Airtable – 8 May 21

On2Air: Actions | On2Air

On2Air: Actions is an Airtable automation and workflow tool to integrate and connect your Airtable data with any other software tools used in your business. Create powerful business processes with almost 20 additional action options beyond the...

[christoph]

@Greg_F Solved it myself. I was starting from the wrong end, made the products my maintable and order the lookup…

[Greg_F]

It would be actually also possible to return array as result of search and link array as multiple links in linked field.

Either way - congrats on successfully hacking to a solution!