Forum Discussion
Multiplication and cut-off value
In fact your formula is finding MINIMUM between multiple of B2 & C2 and the corresponding lookup value is Column G,, and in case of B returns 120,,, but since you have a table matching customers in column A then the used formula is irrelevant !!
This produces the same
=IFERROR(VLOOKUP(A2, $F$2:$G$6, 2, FALSE),B2*C2)
Or even you may use,,
=IFERROR(VLOOKUP(A2, $F$2:$G$6, 2, FALSE),"")
Or you may use this is the best one:
=IFERROR(VLOOKUP($A2,$F2:$F6,1,FALSE),IFERROR(VLOOKUP($A2, $F$2:$G$6, 2, FALSE),$B2*$C2))
- Need to adjust cell references in the formula as needed.
- Check the attached WB.
- The method shown in the attached WB, works with all versions of Excel.
Re: ``your formula is finding MINIMUM between multiple of B2 & C2 and the corresponding lookup value is Column G``
Correct. And that is my interpretation of what DJRM72 wants. He wrote: ``is there a formula that would multiply a quantity of 50 by a rate of €10 but give a cut off value of €100?``
Of course, 50*10 is 500, not 100.
I might also note that DJRM72 did not push back against Peter's similar interpretation. On the contrary, DJRM72 said Peter's interpretation is "perfect".
DJRM72 also seemed to like Peter's idea of putting the customer limits into a table, instead of explicitly in each formula.
I only sought to offer an alternative that, at the very least, is compatible with all versions of Excel. I also believe it is the simpler implementation for all versions of Excel.
-----
Re: ``but since you have a table matching customers in column A then the used formula is irrelevant !! This produces the same =IFERROR(VLOOKUP(A2, $F$2:$G$6, 2, FALSE),B2*C2)``
No, it does not. See my example with customer F. B7*C7 is 200. Since that is less than the (hypothetical) limit of 220 for customer F (G4), my formula returns 200.
- Since you are referring data set for specific customer's cut off then in that case other doesn't matters,, please check the attached sheet with my reply !!