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

[Emilio_Lopez_Ro]

Have you tried using a lookup field? It should work perfectly for what you want to do.

[Jennifer_Horn]

I also have this problem and it’s not at all clear how to use Lookup to accomplish this.

[W_Vann_Hall]

You might want to take a look at this post.

Vlookup Style Search in the Same Table Ask the community

I have a table of tasks that looks something like this: Task Name Owner Deadline Dependencies Task1 Ashley 3/2/2018 none Task2 Bill 3/4/2018 Task1 Task3 Cody 3/5/2018 Task1 I want to add annother column called ‘Dependency Deadline’. For Task2 and Task3 the Dependency Deadline should be 3/2/2018. The goal here is to flag a task that has a deadline before one of its dependencies. I’ve tried using the lookup feature but it requires a Link to a different table, not the same table. In ex…

[Greg_F]

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.

gist.github.com

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

Airtable simple VLOOKUP style script.js

//Substitute "Orders" for table name which contains values
//on which you want to run the vlookup
let mainTable = base.getTable("Orders");
let mainTableRecords = await mainTable.selectRecordsAsync();

//Substitute "Product" for table which contains range to search in
let lookupTable = base.getTable("Products");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

//Replace "Item.barcode" with column name which has the values you want to look up 

This file has been truncated. show original

I have also made a quick video about it:

I hope that helps!

[Onur]

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

[Onur]

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?

[Greg_F]

Onur:

an look up if the lookupValue is in a lookup field

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:

[Greg_F]

Here is a link to a sample base with examples:

Airtable

VLookup - Airtable

Explore the "VLookup" base on Airtable.

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

[Onur]

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