Forum Discussion
Match formula with alternate simple math calculation
Good morning. PLEASE help. I have a formula that almost does everything I need it to do...I have a spreadsheet (sample attached) that I have to have the data that it is pulling from on two separate...
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?
- I am not sure which 0 you are saying to replace?
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.
