Formula for Lookup to return only one option

[Ted_Charles_Bro]

Hello all,

Looking for a formula that takes input from another field to determine which item in the lookup to return.

For example;

LOOKUP FIELD  | OPTION FIELD   | RETURN FIELD
Apple, Orange | 2              | Orange
Apple, Orange | 1              | Apple 

Thanks all

[TheTimeSavingCo]

Hmm, the only way I can think of to do this would be to:

1. Split all the values of the Lookup Field into their own fields
2. Use the value of the Option Field to determine which field from step 1 to display

Very hardcoded, but I can’t think of another way to do this as I don’t think we can extract select items from a comma separated list via formulas alone I’m afraid

[ScottWorld]

@Ted_Charles_Brown

Airtable doesn’t natively allow for dynamic filtering or extracting values.

So I don’t have an answer for you unless you did this in the low-code automation tool Make.com, which would allow you to split a string of text and extract the specific value that you want from it.

If you want to keep this all within Airtable instead of using an external tool like Make.com, your best bet is to use Javascriot to do the same thing (i.e. extract a specific value after splitting a string). I don’t know JavaScript, so the JavaScript experts would need to chime in on this one.

Otherwise, if you just wanted to use Airtable’s built-in formula functions, this would either be completely impossible or extremely tricky. That’s because Airtable has an extremely weak (almost non-existent) formula language. Airtable only gives us a very small handful of extremely basic formula functions to work with, and most of them are not very powerful. if it can be done, it would likely start by including the SUBSTITUTE() function, which is the only function that lets you specify an index value. If anyone can figure it out, it would be someone in these forums, so you’ve come to the right place.

If it can be done with REGEX, Airtable has some extremely basic REGEX support, so there might be some hope there from someone who is a REGEX expert.

But JavaScript or Make are likely your best bets.

You should definitely email support@airtable.com to ask them to include a function to extract a specific index value from an array or comma-separated string. Airtable is absolutely terrible about implementing customer feature requests, but in the hopes that Airtable’s executive management changes in the future, it would be helpful for your email to get in the queue.