# A formula that return solution to 3 X 2 Matrix

#### clemkonan

##### Active Member
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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### SQUIDD

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
Not a problem, happy to be of help.

Dave

Replies
6
Views
329
Replies
5
Views
239
Replies
5
Views
161
Replies
0
Views
314
Replies
2
Views
1K

1,195,749
Messages
6,011,436
Members
441,614
Latest member
TiaGtz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back