Help

Re: Vlookup for Airtable?

3496 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Lisa_Altizer
6 - Interface Innovator
6 - Interface Innovator

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

33 Replies 33

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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

@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?

@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.

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?

@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
10 - Mercury
10 - Mercury

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:

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

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!