=INDEX(COLLECT(

Hello,

I am trying to return a value based on 3 criteria but am getting the #INVALID VALUE error.

Sheet 1 contains my the 3 deciding factors (Single Point of Failure?, Business Impact and/or Safety Related, Lead Time to Procure) and my desired output (Critical Spares Suggested).

Sheet 2 is the user tool that is looking for when the first three columns are met. I wish for Sheet 2's "Critical Spares Suggested" column to match what is shown in Sheet 1.

I have named the manage references according to the column names.

My formula is as follows:

=INDEX(COLLECT({Critical Spares Suggested}, {Single Point of Failure?}, [Single Point of Failure?]@row, {Business Impact and/or Safety Related}, [Business Impact and/or Safety Related]@row, {Lead Time to Procure}, [Lead Time to Procure]@row), 1)

It is returning the #INVALID VALUE error and I cannot figure out why. Can someone point me in the right direction please? Thank you for the help.

Find more posts tagged with

Accepted answers

Bassam Khalil

Hi @Hageman

Hope you are fine, please try the following formula:

=IFERROR(INDEX(COLLECT({Critical Spares Suggested}, {Single Point of Failure?},
 [Single Point of Failure?]@row, {Business Impact and/or Safety Related},
 [Business Impact and/or Safety Related]@row, {Lead Time to Procure},
 [Lead Time to Procure]@row), 1), "")

All comments

Bassam Khalil

Hi @Hageman

Hope you are fine, please try the following formula:

=IFERROR(INDEX(COLLECT({Critical Spares Suggested}, {Single Point of Failure?},
 [Single Point of Failure?]@row, {Business Impact and/or Safety Related},
 [Business Impact and/or Safety Related]@row, {Lead Time to Procure},
 [Lead Time to Procure]@row), 1), "")

Hageman

Hi Bassam,

Thank you very much for your help. The IFERROR addition is brilliant. Since this is a user tool, it also stops the user from receiving errors during the criticality review, allowing them to remain focused during the review.

After entering the formula, I was receiving blank outcomes when the Lead Time to Procure column's selection was "< 24 hours". I am not sure why, but this option seemed to be effecting the formula. I changed the drop-down selections to spell out "Less than 24 hours" and so on to overcome this error. This formula is working great. Thanks again!

Bassam Khalil

@Hageman

Excellent, i will be happy to help you any time. and I will be grateful for your "Vote Up" or "Insightful"

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.