Nested IF problem, Excel Novice here!

Hello,

Basically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...

1.5 O.D. - 16GA - 304SS = $X

but

1.5 O.D. - 18 GA - 304SS = $Y

and such.

The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.

O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.

Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.

=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$20,IF(N7=2=AND(O7=20)=AND(L7=2205),’SteelTubeCostWIP’!$DD$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$17,IF(N7=2=AND(O7=18)=AND(L7=2205),’SteelTubeCostWIP’!$DD$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost WIP’!$DD$13,IF(N7=2=AND(O7=16)=AND(L7=316),’SteelTubeCostWIP’!$DD$14,IF(N7=2=AND(O7=16)=AND(L7=2205),’SteelTubeCostWIP’!$DD$15,IF(N7=1.5=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$10,IF(N7=1.5=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$11,IF(N7=1.5=AND(O7=20)=AND(L7=2205),’SteelTubeCostWIP’!$DD$12,IF(N7=1.5=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$7,IF(N7=1.5=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$8,IF(N7=1.5=AND(O7=18)=AND(L7=2205),’SteelTubeCostWIP’!$DD$9,IF(N7=1.5=AND(O7=16)=AND(L7=304),’Steel Tube Cost WIP’!$DD$4,IF(N7=1.5=AND(O7=16)=AND(L7=316),’SteelTubeCostWIP’!$DD$5,IF(N7=1.5=AND(O7=16)=AND(L7=2205),’SteelTubeCostWIP’!$D$D6,IF(N7=2.5=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$28,IF(N7=2.5=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$29,IF(N7=2.5=AND(O7=20)=AND(L7=2205),’SteelTubeCostWIP’!$DD$30,IF(N7=2.5=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$25,IF(N7=2.5=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$26,IF(N7=2.5=AND(O7=18)=AND(L7=2205),’SteelTubeCostWIP’!$DD$27,IF(N7=2.5=AND(O7=16)=AND(L7=304),’Steel Tube Cost WIP’!$DD$22,IF(N7=2.5=AND(O7=16)=AND(L7=316),’SteelTubeCostWIP’!$DD$23,IF(N7=2.5=AND(O7=16)=AND(L7=2205),’SteelTubeCostWIP’!$DD$24)))))))))))))))))))))))))))

Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?

Thanks,
Nate

Unfortunately I cannot attach this document due to its sensitive nature.

So, in an attempt to clarify, the table is quite complex, and the information is strewn out over several columns, many of which are to be ignored in the formula.

Example:


A-----B-------C------D---------E----------F---------G-------H-----------I----------J-----K------L----

ID - NAME - DESC - DATE - INFO - Material- LENGTH - Tube OD - Gauge - INFO - INFO - INFO



Formula gets that "H(#)=2, I(#)=20, F(#)=304" and matches with some reference table which would be contained on a separate sheet:

=SHEET 2=

--A-----B------C-------D-
-"H"----"I"----"F"------$--
-|2-----20-----304-----$X|
-|2-----20-----316-----$Y| ===========
-|2-----18-----304-----$Z| =AND SO ON=
-|2-----18-----316-----$A| ===========
-|2-----16-----304-----$B|
-|2.5---20-----304-----$C|
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

~It looks at this table to see which cost is the answer, in this case $X.

---

Many of the unique items in this list do not have information for the Material/OD/GA, and thus do not apply to what I am doing.

So again, what I need is this formula to read and say, "okay OD is 2, gauge is 20, and material is 304, that means the cost is $X" essentially reading "hey I have this information, this info matches the info in this reference table, here is X" or I can manually define which cell is X as I did with the IFs, but of course came away with the nesting problem.

I want this to be "living" because the datasheet in question is too (so that once price X is updated, the result also updates accordingly)

Thanks again,
Nate