Hi all - this formula below works well, but it is likely far too messy and wondering if anyone is able to simplify it.
formula:
=IFERROR(IF(A11=0,"",IF(A11="","",IF(ISNUMBER(SEARCH("SA001212",B11)),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000),IF(ISNUMBER(SEARCH("SA000456",B11)),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000),IF(ISNUMBER(SEARCH("30002830",B11)),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000),IF(ISNUMBER(SEARCH("SA000450",B11)),VLOOKUP("Material",CK13c!$C$16:$S$100,16,0)/SUM($N$76/1000),IF(ISNUMBER(SEARCH("Slurry",C11)),VLOOKUP("Material",CK13c!$C$16:$R$100,16,0)/SUM($N$76/1000),INDEX(CK13a!$P$16:$P$100,MATCH(1,INDEX((B11=CK13a!$L$16:$L$100)*(CK13a!$P$16:$P$100>0),0,1),0))))))))),0)
Assume formula is in cell E5 and in the same formula i need to do 1 of 3 things:
- if the number in B5 is: SA001212, SA000456, 30002830 are found then VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000)
- if the number in B5 is: SA001559, SA001311, 30012771 are found then VLOOKUP("Material",CK13c!$C$16:$S$100,16,0)/SUM($N$76/1000)
- if none of the above are found: =VLOOKUP(B5,CK13a!L18:S24,5,0)
i have tried this formula that works.... but i do not know how to expand on it to do all 3 things i need
=IFERROR(LOOKUP(2^15,SEARCH({"SA001212","SA000456","3002830"},B5),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000)),"no")
thanks for any help offered.
formula:
=IFERROR(IF(A11=0,"",IF(A11="","",IF(ISNUMBER(SEARCH("SA001212",B11)),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000),IF(ISNUMBER(SEARCH("SA000456",B11)),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000),IF(ISNUMBER(SEARCH("30002830",B11)),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000),IF(ISNUMBER(SEARCH("SA000450",B11)),VLOOKUP("Material",CK13c!$C$16:$S$100,16,0)/SUM($N$76/1000),IF(ISNUMBER(SEARCH("Slurry",C11)),VLOOKUP("Material",CK13c!$C$16:$R$100,16,0)/SUM($N$76/1000),INDEX(CK13a!$P$16:$P$100,MATCH(1,INDEX((B11=CK13a!$L$16:$L$100)*(CK13a!$P$16:$P$100>0),0,1),0))))))))),0)
Assume formula is in cell E5 and in the same formula i need to do 1 of 3 things:
- if the number in B5 is: SA001212, SA000456, 30002830 are found then VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000)
- if the number in B5 is: SA001559, SA001311, 30012771 are found then VLOOKUP("Material",CK13c!$C$16:$S$100,16,0)/SUM($N$76/1000)
- if none of the above are found: =VLOOKUP(B5,CK13a!L18:S24,5,0)
i have tried this formula that works.... but i do not know how to expand on it to do all 3 things i need
=IFERROR(LOOKUP(2^15,SEARCH({"SA001212","SA000456","3002830"},B5),VLOOKUP("Material",CK13b!$C$16:$S$100,16,0)/SUM($N$68/1000)),"no")
thanks for any help offered.