Forum Discussion
Match formula with alternate simple math calculation
kdwork For instance, you have this:
IFERROR(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)The 0 at the end is returned if the OFFSET function is generating an ERROR. When I selected the next option in the dropdown as you suggested, I got a zero. So, I suspected that one of the three IFERROR functions caused this and wondered if you could perhaps replace each of these zeros with the alternative formula (100-R10-R11-R12). Just an idea.
1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1
MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
I have tried putting the math formula in place of the 0 and it does not work.
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?