Forum Discussion
Match formula with alternate simple math calculation
kdwork Yeah, you are right. Sorry for having confused you. I looked at the formula a bit closer and removed the IFERROR's from it to see what would happen. For the first variety it returns 99.80. For the next one it returns 0.00. Zero is NOT and error, thus the IFERROR will not kick-in.
When I condense the formula, and then wrap it in an IFERROR, it could look like this:
=
IFERROR(
IF(
OR($E$3={"CAN.INTL","BULK"}),
OFFSET(---------),
OFFSET(---------)
),
100-R10-R11-R12
)
meaning that if E3 is either of the two brands, then do the first OFFSET, otherwise do the second OFFSET. Perhaps, then you can trap an error and get the corresponding result.
I tried this, if I am understanding what you are saying above. It says I have entered too many arguments for this function.
=IFerror(if(OR($E$3={"CAN.INTL","BULK"}),OFFSET('Product List ITL_Bulk'!$A$1,MATCH(1,INDEX(($E$3='Product List ITL_Bulk'!$A$2:$A$722)*($C$6='Product List ITL_Bulk'!$D$2:$D$722),,),0),MATCH($S$9,'Product List ITL_Bulk'!$A$1:$CB$1,0)-1,1,1),100-R10-R11-R12),OFFSET('Product List-DOMESTIC'!$A$1,MATCH(1,INDEX(($E$3='Product List-DOMESTIC'!$A$2:$A$605)*($C$6='Product List-DOMESTIC'!$D$2:$D$605),,),0),MATCH($S$9,'Product List-DOMESTIC'!$A$1:$CB$1,0)-1,1,1),100-R10-R11-R12))) the logical test is showing the last part highlighted is what is "too much"
What I don't understand on the original formula, when you choose the second variety, is why is it pulling in 0? Where is that coming from? Where in the formula is it getting that if it doesn't find anything for S9 to return 0?