Forum Discussion
Match formula with alternate simple math calculation
kdwork I am not even going to try decipher that formula, but it seems that it evaluates to zero based on either of the IFERROR functions. Why not include (100-R10-R11-R12) in stead of 0 in these IFERROR functions?
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.
- I am real confused. Aren't the 0's in the formula the match type?
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.