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

	A	B	C	D	E	F	G	H	I
1	length	width	height	Dim	Group
2	10	8	8	640	G1
3	8	5	5	200	G1
4	20	10	10	2000	G2
5	15	10	5	750	G2
6	40	30	10	12000	G3

<tbody>
</tbody>

 

[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