# How to compute Index Match and pull tolerance in a formula

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?

Thanks,

• ✭✭✭✭✭

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!

• edited 02/11/21

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!