Oct 16, 2017 05:38 PM
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
Oct 19, 2017 10:04 AM
Have you tried using a lookup field? It should work perfectly for what you want to do.
Mar 05, 2018 04:27 PM
I also have this problem and it’s not at all clear how to use Lookup to accomplish this.
Mar 05, 2018 08:12 PM
You might want to take a look at this post.
May 16, 2020 08:25 PM
Hi,
This was something that I was looking for as well as I needed to do quite a bit of matching between my tables - that were previously using VLOOKUP in Excel. This did not seem to be possible with regular Airtable functions (and I tried a lot of crazy stuff), so I started looking into the Scripting block.
This actually allowed me to make a perfect solution. OK, almost perfect, you still need to change the name of tables and columns manually in the code, but with comments below this should be fairly straight forward.
I have also made a quick video about it:
I hope that helps!
May 29, 2020 11:22 AM
Continuing the discussion from Vlookup for Airtable?: - This is awesome - exactly what I was looking for. One thing though:
How I can look up if the lookupValue is in a lookup field? (or a formula field that links to another field).
It seems like the cell has no value and therefore the whole lookup runs into nothing.
A way by e.g. extending the code above so that it might solve this, also?
Solution to this is highly appreciated -
Thank you
May 29, 2020 11:30 AM
Another question: how could you define a fallback value (could be a static one “not matched!”) in case that there was no match with the values in the lookup table?
May 29, 2020 08:05 PM
I think both cases can be solved with help of "if"conditions. First one checking if the original column/field is empty (in case you have many empty values there). Second case already has a condition that executed when value is found, so it needs to be completed with else statement to execute action if case of not matching values.
Something like this:
May 29, 2020 09:04 PM
Here is a link to a sample base with examples:
It should be possible to see the code of blocks once you copy the base (let me know otherwise).
Here is also description of the process on Medium (non-paywalled link)
https://medium.com/@greg.business.automated/vlookup-in-airtable-for-tables-without-existing-links
May 30, 2020 11:33 AM
Hi, - this is great & solves the setting of the “else” value!
The first check if not empty => basically does only what is says (gatekeeper for valid values) - but it does not solve the target:
use the value in the fields to look up.
Or maybe its me having a bug?:
=> Use outcome in formula field in another formula field
May 30, 2020 11:57 PM
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:
May 31, 2020 06:30 AM
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?
Jul 23, 2020 08:08 AM
Hi Greg, I tried to access your VLookup base but it says that all of the script blocks are disabled.
Jul 23, 2020 08:31 AM
Hi Brian,
You can try copying the base and the scrip should move with it.
If not you can copy code from here:
Jul 23, 2020 08:41 AM
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:
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.
Jul 23, 2020 10:59 AM
I think it is defined at lines 3. 7, 12, and 17.
Jul 24, 2020 11:05 AM
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?
Jul 28, 2020 08:45 AM
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?
Jul 28, 2020 09:35 AM
Yes, it does, but I think we need to establish a few things first:
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.
Sep 17, 2020 01:13 AM
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?