how to look up a multiple sizes

Seifeddine87

Board Regular
Joined
Sep 7, 2015
Messages
100
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

ABCDEFGHI
1lengthwidthheightDimGroup
21088640G1
3855200G1
42010102000G2
515105750G2
640301012000G3

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

hennahairgel

Board Regular
Joined
Feb 19, 2002
Messages
63
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")))
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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 :)
 
Last edited:

Seifeddine87

Board Regular
Joined
Sep 7, 2015
Messages
100
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,196
Members
414,513
Latest member
junbuggle

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top