Multiple if statements with one of them have ifblank then 0

[savageanman]

Hi,

So I would like to calculate in cell O2 to be result of (weight1(G2) x if 'type of metal' from other sheet(E2)) + (weight2(H2) x if 'type of metal' from other sheet(F2) then if one of these 2 cells blank then 0 ) + (M2/30)

So it goes like this
=((IF(E2="","",G2*IF(E2="925",MA_SETUP!$K$2,IF(E2="10K YG",MA_SETUP!$C$2,IF(E2="10K WG",MA_SETUP!$B$2,IF(E2="10K PG",MA_SETUP!$C$2,IF(E2="14K YG",MA_SETUP!$F$2,IF(E2="14K WG",MA_SETUP!$F$2,IF(E2="14K PG",MA_SETUP!$F$2,IF(E2="18K YG",MA_SETUP!$I$2,IF(E2="18K PG",MA_SETUP!$I$2,IF(E2="18K WG",MA_SETUP!$H$2))))))))))))
+(IF(F2="","",H2*IF(F2="925",MA_SETUP!$K$2,IF(F2="10K YG",MA_SETUP!$C$2,IF(F2="10K WG",MA_SETUP!$B$2,IF(F2="10K PG",MA_SETUP!$C$2,IF(F2="14K YG",MA_SETUP!$F$2,IF(F2="14K WG",MA_SETUP!$F$2,IF(F2="14K PG",MA_SETUP!$F$2,IF(F2="18K YG",MA_SETUP!$I$2,IF(F2="18K PG",MA_SETUP!$I$2,IF(F2="18K WG",MA_SETUP!$H$2,IF(isblank,""))))))))))))))
+(M2/30)

So when there are blank in F3 and H3, the cell O3 show as #value!

How should I go about?

Sorry for my bad English and many thanks in advance.

 

[Special-K99]

You can replace the entire formula with this.

IFE2="","", (G2+H2)*INDEX(MA_SETUP!$A2:K2,1,VLOOKUP(E2,Sheet2!$A$1:$J$2,2,0))

You need a look up table like this. Let's say it's on Sheet2!A1:J2

First column is the E2 values, second column is the index number in the range MA_SETUP!$A$2:$K$2

925 11
10K YG 3
10K WG 2
10K PG 2
14K YG 6
14K WG 2
14K PG 2
18K YG 9
18K PG 9
18K WG 8

IFE2="","", (G2+H2)*INDEX(MA_SETUP!$A2:K2,1,VLOOKUP(E2,Sheet2!$A$1:$J$2,2,0))

For the blank #VALUE error it's probably quicker to put enclose the #VALUE formulas in IFERROR(formula, "")
NOTE: This assumes that F3 H3 and O3 will ALL be blank.