excelif-statementexcel-formula

IF/OR Formula, Compound to Simplified | I want to simplify, narrow down, and have an evolving cell as information is updated on the Excel sheet


I have a cell that I need it to evolve as data is changed, deleted, or added to in the main body of the sheet. (Sheet name "Carton DIM Calculator") I have a very large IF/OR formula that will soon, I suspect, reach a character max point. So before it does that I want to fix it but I'm not sure on where to begin. Also to add to the reasoning I need it fixed is every time I must change information on the sheet I have to go in and change the formula manually. It is time consuming and it's now becoming more and more of a hassle to update. It originally was just a few If/Or's, but our shipping cases have grown from about 10 box sizes to 58 box sizes.

The point of the formula is to return a specified box size based on the dimensions user inputs. Yes, we have a system that does this when a customer places an order and it works 99% flawless on that side. But this side is for setting up new inventory as it comes in the warehouse or when we need to ship something outside of the customer ordering side.

I'm not sure if what I'm currently asking is even achievable or if I will have to manually update this as information changes.

Below is the formula I currently have:

Formula is in Cell F2 Information being called for the formula is between B4 - G4 down to B61 - G61

=IF(OR(B2=0,C2=0,D2=0,B2="",C2="",D2=""),"Pending",
 IF(AND((G2<=G4)*(B2<=B4-0.25)*(C2<=C4-0.25)*(D2<=D4)),"#ENV",
 IF(AND((G2<=G5)*(B2<=B5-0.25)*(C2<=C5-0.25)*(D2<=D5)),"#99",
 IF(AND((G2<=G6)*(B2<=B6-0.25)*(C2<=C6-0.25)*(D2<=D6)),"ENV5",
 IF(AND((G2<=G7)*(B2<=B7-0.25)*(C2<=C7-0.25)*(D2<=D7)),"ENV2",
 IF(AND((G2<=G8)*(B2<=B8-0.25)*(C2<=C8-0.25)*(D2<=D8)),"ENV3",
 IF(AND((G2<=G9)*(B2<=B9-0.25)*(C2<=C9-0.25)*(D2<=D9)),"ENV4",
 IF(AND((G2<=G10)*(B2<=B10-0.25)*(C2<=C10-0.25)*(D2<=D10)),"#30",
 IF(AND((G2<=G11)*(B2<=B11-0.25)*(C2<=C11-0.25)*(D2<=D11)),"#04",
 IF(AND((G2<=G12)*(B2<=B12-0.25)*(C2<=C12-0.25)*(D2<=D12)),"#02",
 IF(AND((G2<=G13)*(B2<=B13-0.25)*(C2<=C13-0.25)*(D2<=D13)),"#49",
 IF(AND((G2<=G14)*(B2<=B14-0.25)*(C2<=C14-0.25)*(D2<=D14)),"#46",
 IF(AND((G2<=G15)*(B2<=B15-0.25)*(C2<=C15-0.25)*(D2<=D15)),"#10_1",
 IF(AND((G2<=G16)*(B2<=B16-0.25)*(C2<=C16-0.25)*(D2<=D16)),"#09",
 IF(AND((G2<=G17)*(B2<=B17-0.25)*(C2<=C17-0.25)*(D2<=D17)),"#10",
 IF(AND((G2<=G18)*(B2<=B18-0.25)*(C2<=C18-0.25)*(D2<=D18)),"#43",
 IF(AND((G2<=G19)*(B2<=B19-0.25)*(C2<=C19-0.25)*(D2<=D19)),"#06",
 IF(AND((G2<=G20)*(B2<=B20-0.25)*(C2<=C20-0.25)*(D2<=D20)),"#45",
 IF(AND((G2<=G21)*(B2<=B21-0.25)*(C2<=C21-0.25)*(D2<=D21)),"#03_1",
 IF(AND((G2<=G22)*(B2<=B22-0.25)*(C3<=C22-0.25)*(D2<=D22)),"#13_1",
 IF(AND((G2<=G23)*(B2<=B23-0.25)*(C2<=C23-0.25)*(D2<=D23)),"#03",
 IF(AND((G2<=G24)*(B2<=B24-0.25)*(C2<=C24-0.25)*(D2<=D24)),"#48",
 IF(AND((G2<=G25)*(B2<=B25-0.25)*(C2<=C25-0.25)*(D2<=D25)),"#42",
 IF(AND((G2<=G26)*(B2<=B26-0.25)*(C3<=C26-0.25)*(D2<=D26)),"#13",
 IF(AND((G2<=G27)*(B2<=B27-0.25)*(C2<=C27-0.25)*(D2<=D27)),"#05",
 IF(AND((G2<=G28)*(B2<=B28-0.25)*(C2<=C28-0.25)*(D2<=D28)),"#08",
 IF(AND((G2<=G29)*(B2<=B29-0.25)*(C2<=C29-0.25)*(D2<=D29)),"#07_1",
 IF(AND((G2<=G30)*(B2<=B30-0.25)*(C2<=C30-0.25)*(D2<=D30)),"#41",
 IF(AND((G2<=G31)*(B2<=B31-0.25)*(C2<=C31-0.25)*(D2<=D31)),"#33_1",
 IF(AND((G2<=G32)*(B2<=B32-0.25)*(C2<=C32-0.25)*(D2<=D32)),"#22",
 IF(AND((G2<=G33)*(B2<=B33-0.25)*(C2<=C33-0.25)*(D2<=D33)),"#26_1",
 IF(AND((G2<=G34)*(B2<=B34-0.25)*(C2<=C34-0.25)*(D2<=D34)),"#32_1",
 IF(AND((G2<=G35)*(B2<=B35-0.25)*(C2<=C35-0.25)*(D2<=D35)),"#25_1",
 IF(AND((G2<=G36)*(B2<=B36-0.25)*(C2<=C36-0.25)*(D2<=D36)),"#14_2",
 IF(AND((G2<=G37)*(B2<=B37-0.25)*(C2<=C37-0.25)*(D2<=D37)),"#07",
 IF(AND((G2<=G38)*(B2<=B38-0.25)*(C2<=C38-0.25)*(D2<=D38)),"#24_1",
 IF(AND((G2<=G39)*(B2<=B39-0.25)*(C2<=C39-0.25)*(D2<=D39)),"#32",
 IF(AND((G2<=G40)*(B2<=B40-0.25)*(C2<=C40-0.25)*(D2<=D40)),"#33",
 IF(AND((G2<=G41)*(B2<=B41-0.25)*(C2<=C41-0.25)*(D2<=D41)),"#21_1",
 IF(AND((G2<=G42)*(B2<=B42-0.25)*(C2<=C42-0.25)*(D2<=D42)),"#31",
 IF(AND((G2<=G43)*(B2<=B43-0.25)*(C2<=C43-0.25)*(D2<=D43)),"#20_1",
 IF(AND((G2<=G44)*(B2<=B44-0.25)*(C2<=C44-0.25)*(D2<=D44)),"#26",
 IF(AND((G2<=G45)*(B2<=B45-0.25)*(C2<=C45-0.25)*(D2<=D45)),"#24",
 IF(AND((G2<=G46)*(B2<=B46-0.25)*(C2<=C46-0.25)*(D2<=D46)),"#25",
 IF(AND((G2<=G47)*(B2<=B47-0.25)*(C2<=C47-0.25)*(D2<=D47)),"#21",
 IF(AND((G2<=G48)*(B2<=B48-0.25)*(C2<=C48-0.25)*(D2<=D48)),"#23_2",
 IF(AND((G2<=G49)*(B2<=B49-0.25)*(C2<=C49-0.25)*(D2<=D49)),"#14_1",
 IF(AND((G2<=G50)*(B2<=B50-0.25)*(C2<=C50-0.25)*(D2<=D50)),"#20",
 IF(AND((G2<=G51)*(B2<=B51-0.25)*(C2<=C51-0.25)*(D2<=D51)),"#15_1",
 IF(AND((G2<=G52)*(B2<=B52-0.25)*(C2<=C52-0.25)*(D2<=D52)),"#23_1",
 IF(AND((G2<=G53)*(B2<=B53-0.25)*(C2<=C53-0.25)*(D2<=D53)),"#14",
 IF(AND((G2<=G54)*(B2<=B54-0.25)*(C2<=C54-0.25)*(D2<=D54)),"#23",
 IF(AND((G2<=G55)*(B2<=B55-0.25)*(C2<=C55-0.25)*(D2<=D55)),"#15",
 IF(AND((G2<=G56)*(B2<=B56-0.25)*(C2<=C56-0.25)*(D2<=D56)),"#51X_2",
 IF(AND((G2<=G57)*(B2<=B57-0.25)*(C2<=C57-0.25)*(D2<=D57)),"#51X_1",
 IF(AND((G2<=G58)*(B2<=B58-0.25)*(C2<=C58-0.25)*(D2<=D58)),"#51X","OS3"
))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Images to help:

  1. B2-E2 are 'user' input, F2 is above formula, G2 has a working formula as does H2

B2-E2 are 'user' input, F2 is above formula, G2 has a working formula as does H2

  1. These are usually hidden from user and these cells are locked to keep anyone from accidentally changing the values
    glimpse of the cells and what they look like

Cannot add entire image captured, but this is a glimpse of the cells and what they look like. These are usually hidden from user and these cells are locked to keep anyone from accidentally changing the values.

Thank you.

I just keep editing the current formula but it is becoming more and more cumbersome to keep up and change. Simply seeking help on how I can fix this and have no idea where to begin.


Solution

  • Lookup with Multiple Criteria in an Excel Table

    =IF(COUNTBLANK(B2:D2)>0,"Pending",
        IFERROR(INDEX(Table1[Container Type],MATCH(1,(G2<=Table1[Volume])
            *(B2<=(Table1[Full Length]-0.25))*(C2<=(Table1[Full Width]-0.25))
            *(D2<=(Table1[Full Height])),0)),"OS3"))
    

    enter image description here