Skip to main content

Hello!

I am trying to create a field that returns matching values from 2 other fields.

In the screenshot below, ‘Budget’ and ‘Offered’ have the same options (multi-select fields). I would like to have the third field, ‘Budgeted and Offered’ that shows the matching options from those fields. 

 

 

How can this be accomplished within airtable?

Hi,
When your list of options is stable and changed not often, you can hardcode them all in formula like
 

IF(AND(FIND("unreleased,",Budget),FIND("unreleased,",Offered)),"unreleased ")&
IF(AND(FIND("two",Budget),FIND("two",Offered)),"two ")&
IF(AND(FIND("finishes",Budget),FIND("finishes",Offered)),"finishes ")&
IF(AND(FIND("walls",Budget),FIND("walls",Offered)),"walls ")&
IF(AND(FIND("released,",Budget),FIND("released,",Offered)),"released ")&

And edit it each time you add new option
To quickly do it from scratch, duplicate any of the columns, preferably containing all available options seen. Then turn the duplicate into Linked field to a new table.
Then switch to this new table to see smth like:
 

Primary field will be a list of options.
Add formula like:

 

'IF(AND(FIND("'&Name&'",Budget),FIND("'&Name&'",Offered)),"'&Name&' ")&'

to get
 

Copy-paste Calculation field and insert into your table as new formulka field. Don’t forget to erase last ampersand
Then you can delete New created table. Each time you add new option, add it in formula as well.

The only problem is when one of your options word is also a part of other option. Notice how in first upper record word ‘released’ appear despite being absent in Offered. It’s because formula consider it as present (in word ‘unreleasedl

 

 


The second way is when your list of options is dynamic, often changes and it’s crazy to maintain formula to be always updated. This way is better by logic, as you using the right data type for such things. But it’s not so beautiful. 
Just create a table of options, and turn both fields to link fields (to the table of options)
Sorry  I cannot tell right now how to get a common options, but if you are interested just let me know  (here) and I will desribe the process (I hope)


Reply


OSZAR »