Perform a calculation based on criteria found using Index Match

  • I am using this formula for now:

    =IF(INDEX('Supplier Data'!C:C,MATCH('Supplier Eval'!A3,'Supplier Data'!A:A,0))<1000000,"zero allowed","1 per M")

    If the index match result is "zero allowed" I need to Index the value in 'Supplier Data'!D:D with same match arguments and if the returned value is >zero the formula should return -1 if <zero return +1
    If the index match result is "1 per M" I need the formula to Index 'Supplier Data'!D:D again with same match arguments and if that value is >1 per million (the index match value will be an number/integer) return a value of -1, if <1 per million return +1.
    If you can figure this out you are awesome!

    Thank you,

  • I was able to replace the arguments "zero allowed" and "1 per M" with IF functions to complete the formula. If anybody is interested in seeing it let me know.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!