Link formula like Vlookup

[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]

I have just read the other thread and I think the bug was due to whitespaces etc…

Either way Bill’s solution with hash table is a definitely faster way! Hats off and link below for anyone who might stumble here:

Use outcome in formula field in another formula field Formulas

It’s a replacement for the let returnValue = line… Here’s a new version that works: [image] /* *********************************************************** Airdrop - Fast Lookup Copyright (c) 2020 by Global Technologies Corporation ALL RIGHTS RESERVED *********************************************************** */ // display app title output.markdown('# Perform Fast Lookup'); // get the main table data let mainTable = base.getTable("T1"); // get the data set for the main…

[Bill_French]

Onur:

but it does not solve the target: use the value in the fields to look up.

Hmmm, I thought that was the entire point of this script example - to use each value in the primary table to lookup a record that matches in the secondary table and update a column back in the primary table.

This is precisely the pattern I use to do this and often. Pretty sure it’s doing that. If it wasn’t, how would explain these leading-zero values and one “Nothing Found” in the primary table?

[Brian_Schuster]

Hi Greg, I tried to access your VLookup base but it says that all of the script blocks are disabled.

[Greg_F]

Greg_F:

https://gist.github.com/gregonarash/f1e8ce0b81141c6b3f4b023ffd6a8027

Hi Brian,
You can try copying the base and the scrip should move with it.
If not you can copy code from here:

https://gist.github.com/gregonarash/f1e8ce0b81141c6b3f4b023ffd6a8027

[Brian_Schuster]

Thanks, I found the script in your Medium article too. How can I feed data about which tables to look in and which fields to search from a record’s properties?

I am setting up a base called “Search” that has the following properties:

• Name of base to search
• Output column to generate the match list (record IDs)
• Column to search
• Query text
• Exact match or contains
• Parent filter

A parent filter is one that dictates which records a child filter can use, so it could speed up the child’s search by already having eliminated some possibilities.

I would like the table to calculate all the records that match the search-filter query as a list of record IDs. The result would look like this: rec6a2kirXC7AESjq ;; recYo9A3ylGxI8w0A ;; recr59z46kzIoZMFT for three matches or much longer when there are many matches.

[Bill_French]

Brian_Schuster:

How can I feed data about which tables to look in and which fields to search from a record’s properties?

I think it is defined at lines 3. 7, 12, and 17.

[Brian_Schuster]

The problem is that I would need to write the column names into the code. I want the code to repeat for every record in a table when I call the VLookup function. Each record where it runs would have a property for each of the inputs and outputs. Does that make sense?

[Brian_Schuster]

I basically need a Vlookup where I define the columns and cells within the formula, just like Excel. Is that possible to do with the Scripting block?

[Bill_French]

Brian_Schuster:

I want the code to repeat for every record in a table when I call the VLookup function. Each record where it runs would have a property for each of the inputs and outputs. Does that make sense?

Yes, it does, but I think we need to establish a few things first:

• I responded to a specific question about the points in someone else’s code where the table and field names were specified.
• I did so only to accelerate your changes to that code.
• The strategy is now on a new compass heading; one that I’m not entirely understanding mostly because I’m working without any indication of a specific objective in the context of a specific table and data.

Brian_Schuster:

I basically need a Vlookup where I define the columns and cells within the formula, just like Excel. Is that possible to do with the Scripting block?

Pretty much all aspects of lookup automation are possible with Script Blocks; it’s a simple matter of code. But, it’s very difficult to recommend a specific approach with so few details and especially so as an addendum to a thread that began in 2017.

I recommend you head on over tho the Script Block part of the community and present your requirements for your specific objective. I would leave out “VLookup”, and mentions of “just like Excel” because these are biased suppositions of an implementation approach and may work against you. Airtable is not Excel nor is it a “spreadsheet”.

Ideally, a requirements objective is void of implementation details or suggestions of a particular approach and will make it far easier for other community experts to better understand the business goal. Implementation ideas will probably then be in abundance.

[Luxy]

Great script @Greg_F! Thanks for helping and contributing.

Is there a way to let this script run automatically, so I don’t have to trigger it manually each time when a new record is added?