=INDEX(COLLECT(

Hageman

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.

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), "")

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"