how to look up a multiple sizes

[Seifeddine87]

hi i have got multiple sizes of stock and i want to separate them in a different groups
for example

Group 1 (G1) will be less the 10-8-8 640
Group 2 (G2) bigger then G1 and less then 20-10-10 2000
Group 3 (G3) bigger then G2 and less then 40-30-10 12000

i want a formula that will compare L,W,H and dim to give me the right group number

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]length[/TD]
[TD]width[/TD]
[TD]height[/TD]
[TD]Dim[/TD]
[TD]Group[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]640[/TD]
[TD]G1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]200[/TD]
[TD]G1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]2000[/TD]
[TD]G2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]750[/TD]
[TD]G2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]12000[/TD]
[TD]G3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hennahairgel]

In E2 try:
=IF(AND(A2<=10,B2<=8,C2<=8,D2<=640),"G1",IF(AND(A2<=20,B2<=10,C2<=10,D2<=2000),"G2",IF(AND(A2<=40,B2<=30,C2<=10,D2<=12000),"G3","Over")))

 

[Special-K99]

in E2
="G"&LOOKUP(D2,{0,640,641,2000,12000},{1,1,2,2,3})
and copy down

 

[Special-K99]

In E2 try:
=IF(AND(A2<=10,B2<=8,C2<=8,D2<=640),"G1",IF(AND(A2<=20,B2<=10,C2<=10,D2<=2000),"G2",IF(AND(A2<=40,B2<=30,C2<=10,D2<=12000),"G3","Over")))

Forum is showing #REF in your formula though the quote in the edit of this reply (while Im editing) shows proper cell references ??

UPDATE: Aaah, looks like you was editing it as I was replying

 

[hennahairgel]

Yup, I mucked up my copy/paste!

 

[Seifeddine87]

in E2
="G"&LOOKUP(D2,{0,640,641,2000,12000},{1,1,2,2,3})
and copy down

thank you for your help
this form will compare only the Dim mean if i have got one size 15-2-2 will go to G1 but i want it in G2 because the length more then 10

 

[Seifeddine87]

In E2 try:
=IF(AND(A2<=10,B2<=8,C2<=8,D2<=640),"G1",IF(AND(A2<=20,B2<=10,C2<=10,D2<=2000),"G2",IF(AND(A2<=40,B2<=30,C2<=10,D2<=12000),"G3","Over")))

works perfect thank you