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 * INJ - Critical Tolerance (always fixed values - no user interaction) * 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,

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

INJ - Critical Tolerance (always fixed values - no user interaction)
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,

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.