How to compute Index Match and pull tolerance in a formula

Isaac Gallardo

Good afternoon,

I wonder if someone could help me to write the following formula. I have been trying for a couple of hours with no luck. I create two separate sheets

1. INJ - Critical Tolerance (always fixed values - no user interaction)
2. INJ - QUATO - Setup Sheet (User will specify platform and Parameter from an online form)

I want to pull the USL and LSL from the tolerance sheet and compute the results in the USL-SPEC

example:

If Platform and Parameter match in both sheets, I want to compute the USL and LSL taking into consideration the target value in the USL-SPEC column.

Target: 100

USL: 100.5

LSL: 99.25

Any Idea how to do that?

Your help is much appreciated!

Thanks,

David Joyeuse

Hi @Isaac Gallardo

Try this:

USL-PEC column:

=IFERROR(100 + VALUE(INDEX(COLLECT({INJ - Critical Tolerances - USL}, {INJ - Critical Tolerances - Platform}, Platform@row, {INJ - Critical Tolerances - Parameter} , Parameter@row), 1)), "")

LSL-PEC Column:

=IFERROR(100 - VALUE(INDEX(COLLECT({INJ - Critical Tolerances - LSL}, {INJ - Critical Tolerances - Platform}, Platform@row, {INJ - Critical Tolerances - Parameter} , Parameter@row), 1)),"")

Hope it helped!

Isaac Gallardo

David,

I have been trying to understand your formula. Could you please explain why you have a COLLET, INDEX, and value also and IFERROR?

The formula is not working as it should be, it's only giving me a blank statement. But, I think this is the right approach.