Nested IFERROR

Options
Jeremy_P
Jeremy_P
edited 12/09/19 in Formulas and Functions

I am using the below formula that is working fine:

=IFERROR(IFERROR(INDEX({Sales_Parts_1_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_1_PN}, 0)), INDEX({Sales_Parts_2_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_2_PN}, 0))), "ERROR - CHECK PART #")

However, I want to add another INDEX statement.  When I do, I get an #incorrect argument set error.  Is the below even possible?

=IFERROR(IFERROR(INDEX({Sales_Parts_1_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_1_PN}, 0)), INDEX({Sales_Parts_2_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_2_PN}, 0)), INDEX({Sales_Parts_3_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_3_PN}, 0)), "ERROR - CHECK PART #"))

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Couple things I would check if I were you... Make sure you are closing all of your IFERRORS correctly. Based on a quick glance it looks like you are not closing them right. 

    You were trying to close both of the IFERRORS at the end. 

    Try this slight modification and let me know if it works. 

    =IFERROR(IFERROR(INDEX({Sales_Parts_1_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_1_PN}, 0)), INDEX({Sales_Parts_2_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_2_PN}, 0))), INDEX({Sales_Parts_3_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_3_PN}, 0)), "ERROR - CHECK PART #")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    One thing I have found that helps when nesting more complicated and/or longer formulas is to break them out, ignore the data itself and start with nesting the basic syntax.

    .

    =IFERROR(first formula, second formula)

    .

    You already have the first formula worked out, so leave that portion as is. Figure out what you want the second portion to be and work that out as a stand alone.

     

    Once you have that working, you can drop that into place where it says second formula.

     

    Then you can go ahead and take that first formula (IFERROR included) and drop that into the first formula portion.

    .

    I have found that it really helps with making sure each portion is closed out the way it needs to be, and it helps with trouble shooting because you have each portion broken out already.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!