A formula that return solution to 3 X 2 Matrix

[clemkonan]

I have a spreadsheet designed to provide a supplier approval method based on if the supplier is low risk (LW) or not low risk (NLR). The solution defaults to a 3 X 2 table where:

C 2= Manufacturer
D 2 =Distributor
E 2 = Packer

B 3 - Low risk
D 3 = Not Low Risk

From this we have:
C 3 = "GFSI"
C 4 = "GFSI"
D 3, D 4, E 3 , E 4 each = "Quality Watch"

At a vacant cell I was trying to use=IF(H2="LR",OR(H2="NLR",AND(M2="Mfc.","GFSI ","Quality Watch")))

In plain English this means
1) if you are a manufacturer and your ingredient is low risk or high risk we approve you based on you having a certificate.
2) if you are a distributor meaning you stock and ship or a packer meaning you buy in bulk, blend and package then we put you on a quality watch program.
Looks like a nested IF statement but not sure.

Thanks for your help

 

[SQUIDD]

Hi clemkonan

Well, a little confused ay you have told us what is in your table, but then your formaul does not refer to the table at all.
You refer to H2 and M2 within your formula.

I have re written your formula. I thinks its what you want, if not let me know.

=IF(AND(OR(H2="LR",H2="NLR"),M2="mfc"),"GFSI","Quality Watch")

cheers

Dave

 

[clemkonan]

Hi clemkonan

Well, a little confused ay you have told us what is in your table, but then your formaul does not refer to the table at all.
You refer to H2 and M2 within your formula.

I have re written your formula. I thinks its what you want, if not let me know.

=IF(AND(OR(H2="LR",H2="NLR"),M2="mfc"),"GFSI","Quality Watch")

cheers

Dave

Sorry about that , good catch, the formula works well but I messed up in limiting column H ( Supplier Class) to "LR" and "NLR". I have to also cover an entry level requirement or baseline, meaning that if a supplier cannot meet a minimum standard they get de-listed or rejected. This adds a third possibility potentially called " Base"and creates a 3 X 4 Matrix .If H2 = "base" the output should be " Delist"

I have two options for column "H" I could add a suffix and make the entries "LRH", "NLRH" "LRO" and" NLRO" where "O" means missing the baseline or simply use " LR", " NLR" and "Base" .
Hope this makes sense and how would the formula reflect this change?

Thanks again.

Thanks

 

[SQUIDD]

Hi

so this is the H2 = bast then it will be delist, anything else the previos formula stands.

=IF(H2="base","Delist",IF(AND(OR(H2="LR",H2="NLR"),M2="mfc"),"GFSI","Quality Watch"))

dave

 

[clemkonan]

Hi

so this is the H2 = bast then it will be delist, anything else the previos formula stands.

=IF(H2="base","Delist",IF(AND(OR(H2="LR",H2="NLR"),M2="mfc"),"GFSI","Quality Watch"))

dave

Fantastic thanks so very much.This is a huge assist.

 

[SQUIDD]

Not a problem, happy to be of help.

Dave