Comparing Two Categories with Multiple Selection Values

[Todd_Wallenstei]

I am brand new to Airtable.
I would like to compare a product grade to a supplier grade.
A Supplier with an “A” grade can make “A”, “B”, “C”, or “D” product.
A Supplier with a “B” grade can make “B”, “C”, or “D” product, and so on.
I am pulling from two different tables; the product grade from a product table, and supplier grade from a supplier table. This third table would simulate a Purchase Order, where a single supplier may be making multiple products.

I was able to create a formula to check if the supplier is allowed to make that part, however if I add multiple parts, the formula doesn’t work. Is there a better way to approach this?

IF(AND({Product Category}=“A”,{Supplier Category}=“A”),“OK”,IF(AND({Product Category}=“A”,{Supplier Category}=“B”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“A”,{Supplier Category}=“C”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“A”,{Supplier Category}=“D”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“B”,{Supplier Category}=“A”),“OK”,IF(AND({Product Category}=“B”,{Supplier Category}=“B”),“OK”,IF(AND({Product Category}=“B”,{Supplier Category}=“C”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“B”,{Supplier Category}=“D”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“C”,{Supplier Category}=“A”),“OK”,IF(AND({Product Category}=“C”,{Supplier Category}=“B”),“OK”,IF(AND({Product Category}=“C”,{Supplier Category}=“C”),“OK”,IF(AND({Product Category}=“C”,{Supplier Category}=“D”),“Supplier is not qualified to Manufacturer this part”,""))))))))))))

[Todd_Wallenstei]

I was able to significantly improve my original formual and fix one issue where multiple Part Numbers were selected. It now calculates for each instance.

Any thoughts on further improvements?

IF(AND(FIND(“A”,{Product Category}),FIND(“A”,{Supplier Category}))=1,“OK”, IF(AND(FIND(“B”,{Product Category}),FIND(“A”,{Supplier Category}))=1,“OK”, IF(AND(FIND(“B”,{Product Category}),FIND(“B”,{Supplier Category}))=1,“OK”, IF(AND(FIND(“C”,{Product Category}),FIND(“A”,{Supplier Category}))=1,“OK”, IF(AND(FIND(“C”,{Product Category}),FIND(“B”,{Supplier Category}))=1,“OK”, IF(AND(FIND(“C”,{Product Category}),FIND(“C”,{Supplier Category}))=1,“OK”,
“Not Qualified”))))))