Embedded IF and IF Error Formula

Options

Hi there,

I'm trying to use an IF and IF ERROR formula with an embedded index + match formula, so that if doesn't find the information on the one reference sheet it looks at the next sheet and then the next sheet.

=IF(IFERROR(INDEX({B16.P21 QF}, MATCH([Function/Task ID]@row, {B16.P21 ID}, 0))), IFERROR(INDEX({B16.P20 QF}, MATCH([Function/Task ID]@row, {B16.P20 ID}, 0))), IFERROR((INDEX({B16.P19 QF}, MATCH([Function/Task ID]@row, {B16.P19 ID}, 0))))

An incorrect argument message is being returned.

What am I doing wrong?

Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    HI @Jack Parry

    Looks like there was a couple of things there, you were closing out the IFERRORs before moving on to the next one instead of stacking them and there were errant close brackets at the end.

    Without recreating the sheets etc. I cant test but think the below may work.

    You don't require the initial IF function as the IFERROR is doing that for you

    =IFERROR(INDEX({B16.P21 QF}, MATCH([Function/Task ID]@row, {B16.P21 ID}, 0)), IFERROR(INDEX({B16.P20 QF}, MATCH([Function/Task ID]@row, {B16.P20 ID}, 0)), IFERROR(INDEX({B16.P19 QF}, MATCH([Function/Task ID]@row, {B16.P19 ID}, 0)),"Error")))

    Let me know how it goes

    Thanks

    Paul

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    HI @Jack Parry

    Looks like there was a couple of things there, you were closing out the IFERRORs before moving on to the next one instead of stacking them and there were errant close brackets at the end.

    Without recreating the sheets etc. I cant test but think the below may work.

    You don't require the initial IF function as the IFERROR is doing that for you

    =IFERROR(INDEX({B16.P21 QF}, MATCH([Function/Task ID]@row, {B16.P21 ID}, 0)), IFERROR(INDEX({B16.P20 QF}, MATCH([Function/Task ID]@row, {B16.P20 ID}, 0)), IFERROR(INDEX({B16.P19 QF}, MATCH([Function/Task ID]@row, {B16.P19 ID}, 0)),"Error")))

    Let me know how it goes

    Thanks

    Paul

  • Jack Parry
    Jack Parry ✭✭✭✭
    Options

    @Paul McGuinness Worked perfectly! Thank you :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!