How to use a Numeric Value as the Lookup Value

[Jessica_Jones]

Hi,

I’d like to conduct a lookup to group items into classes based on numeric values. I have a table that already does this. It’s called TextileWeightKey and it looks like this:

In case the picture doesn’t load, the important columns in this key table are WeightCode (the primary key), MinWeight, and MaxWeight. The first record has the values L1, 0, and 33.9 respectively. The second record has the values L2, 33.9 and 67.8, and so on. These represent different classes of textile weights. All of the L1 class textiles will weigh between 0 and 33.9 grams per square meter (gsm), all of the L2 class textiles will weight between 33.9 and 67.8 gsm, and so on.

In another table, Textiles, I have a records representing many unique textiles. Each individual textile has a specific weight, such as 17.4 gsm and there is a field in this table (TextileWeight) that receives this value from those doing the data entry.

I would like to have a lookup field in the Textiles table called TextileWeightCode that displays the weight code for that class of the textile. To do this, I’d like to lookup the actual textile weight (17.4 in this example), compare it with the MinWeight (and/or MaxWeight) field of the TextileWeightKey table, and return the value of the WeightCode field (L1 in this example).

I believe one reason I am unable to do this currently is that field I am conducting my lookup search in (MinWeight) is not the primary key of TextileWeightKey. But, to thest this, I have made a duplicate table in which the primary key IS the mininum weight field and it still doesn’t work.

The problem is that when I try to create a lookup field in the Textiles table, it won’t even let me select the TextileWeight field (or any other field that contains numeric values) as the reference field whose values will be looked up in the TextileWeightKey table (no matter how that table is structured).

Is there any way to do what I want to do?

[kuovonne]

Welcome to the Airtable community!

You may be expecting a lookup field to behave differently from what it does. Lookup fields display a value from a record that has already been linked to the current record. This support article has more information.

Airtable lookup fields do not behave like vlookup in Excel.

If you have a Pro subscription, you could use an automation script to link the record to the proper TextileWeightCode based on the TextileWeight.

[Jessica_Jones]

Thanks for responding! I do have a pro account.

I understand that Excel’s vlookup and Airtable’s lookup are different. I am not looking to replicate an exact mechanism from Excel, just to accomplish my goal of assigning WeightCodes to individual textile records based on their actual numeric weights.

You mentioned:

kuovonne:

Lookup fields display a value from a record that has already been linked to the current record.

What I am trying to figure out is HOW to make that link given that the piece of info the two tables hold in common is a numeric piece of data.

[ScottWorld]

Jessica_Jones:

What I am trying to figure out is HOW to make that link given that the piece of info the two tables hold in common is a numeric piece of data.

As @kuovonne mentioned, Airtable doesn’t give you the native ability to automatically make that link.

You have to manually make the links yourself.

Alternatively, you can write your own JavaScript code that would automatically make the link for you via an Airtable Automation.

You could also turn to an external automation tool like Integromat.

[kuovonne]

Jessica_Jones:

What I am trying to figure out is HOW to make that link

There are only a handful of ways of making a link:

• manually in the user interface
• with code (such as an automation script or scripting app)
• with a combination of a formula field to calculate the code and an Airtable automation
• with a third party integration/automation

You cannot make the link using only a lookup or formula field.

[ScottWorld]

kuovonne:

You cannot make the link using only a lookup or formula field.

I was just thinking about how this is one of the top questions that repeatedly comes up in the forums, and it was also one of the things that confused me the most when I first started using Airtable. It seems like it would be extremely helpful if Airtable added this functionality into Airtable. It just came up again a few seconds ago in the newest forum post. We get this question multiple times per day.

@Adam_Minich @Taylor_Savage @Jason

[kuovonne]

ScottWorld:

I was just thinking about how this is one of the top questions that repeatedly comes up in the forums, and it was also one of the things that confused me the most when I first started using Airtable.

How Airtable handles linked records is one of Airtable’s quirks. Airtable’s way of handling linked records is significantly different from both how traditional relational databases handle relationships and how spreadsheets lookup relational data.

However, this particular quirk is so deeply embedded into Airtable’s data model that I don’t see it changing any time soon. Linking via automations goes a long way to help bridge this gap, but it is not intuitive, a bit tricky to setup, and can cost a lot of automation runs.

[Jessica_Jones]

Thank you Kuovonne and ScottWorld for your help! I definitely understand the Airtable capabilities (and lack thereof) better now. It’s a shame this isn’t possible without automations. But I really appreciation the fast and helpful responses.

[ScottWorld]

kuovonne:

However, this particular quirk is so deeply embedded into Airtable’s data model that I don’t see it changing any time soon. Linking via automations goes a long way to help bridge this gap, but it is not intuitive, a bit tricky to setup, and can cost a lot of automation runs.

True. The bigger problem here is that this can’t be done without introducing a script into the automation.

What I think is most missing from the Automations feature is the ability to “find/search” for a record based on a specified criteria, and then update the found record(s). That would go a long way towards solving this problem for everybody.

In other words, the big “missing killer feature” from Automations is that we can’t perform searches for records. Automations only allow us to update records by their Record ID, but sometimes we need to search for a record in another table in order to find the Record ID to begin with.

Currently, this can only be accomplished with JavaScript scripting, but that isn’t really user-friendly enough for many Airtable users.

@Adam_Minich @Taylor_Savage @Jason

[kuovonne]

ScottWorld:

The bigger problem here is that this can’t be done without introducing a script into the automation.

It is currently possible to use automations to link the triggering record to a different record without a script. It is extremely limited. But it is possible.

You are correct that it isn’t currently possible to search for a record with an automation without using code.