Formula: Return record from one table that matches conditions to several fields in another table

[Engineer21]

Dear community,

I have two tables, one called Supply and one called Demand, full of engineering information. There could be several Supply records that match the properties of Demand records.

I am looking to create a formula in the Demand table that returns the cell 'Specific Material' from the Supply table records, for each Demand record, via IF AND OR conditions.

I don't know whether this is possible, and below is my attempt referring to the Supply table via 'Supply.' then the Supply field name. The Demand fields referenced all show as purple, indicating that they are correctly identified, but the Supply fields are all still black.

The conditions are all AND except for Supply.X material colour, which could be a match to either Translucent Colour or X Material colour in the Demand table. One of these should match as the last AND condition being met.

Could anyone help me solve this via formula? I'd prefer a formula solution to a suggestion to use Lookup fields.

I had this working nicely in Excel. 

Thank you in advance!

```python

IF(

    AND(

        {Supply.Highest Target viscosity} >= {Lowest Target viscosity}, {Supply.Lowest Target viscosity} <= {Highest Target viscosity}, {Market} = {Supply.Market}, {X material} = {Supply.X material},

OR(

{Supply.PCR material colour} = {Translucent Colour}, {Supply.X material colour} = {X material colour}

    )), 'Supply.Specific Material'

)

```

[ScottWorld · Accepted solution]

Unfortunately, that is not possible in Airtable. Remember that Excel is more powerful than Airtable in many ways.

You might be able to create an automation that finds the right match for you, but there is no way to do that with formulas unless you create a bunch of lookup fields.

For advanced automations, you may need to use scripting or Make.

[Engineer21]

Thank you for your answer @ScottWorld 

I was worried as much that it wouldn't be possible that way via formula. I will have to think about re-structuring with a bunch of lookup fields or an automated script solution.