Adding an OR to an IFERROR

Options

Hi Community,

I have a formula that looks up an ID I enter in a new sheet and pulls in its title from an existing sheet.

=IFERROR(IF(ISBLANK(Task@row), "", INDEX({Master Range 1}, MATCH(Task@row, {Master Range 2}, 0))), "")

This works great, but now I want to tell it to search 2 different sheets to search for the title. Individually it would be:

=IFERROR(IF(ISBLANK(Task@row), "", INDEX({Slave List Range 1}, MATCH(Task@row, {Slave List Range 2}, 0))), "")

But can I combine both of those formulas in an OR so that I enter my ID, and it looks in 1 sheet or another to find and populate the title?

Thanks!

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Yes - you're telling the formula to look at itself here and it can't do this.

    If you just want it to check the 2 sheets without anything else, then you would use:

    =IFERROR(INDEX({Sheet 1 Title}, MATCH(Task@row, {Sheet 1 ID}, 0)), IFERROR(INDEX({Sheet 2 Title}, MATCH(Task@row, {Sheet 2 ID}, 0)), ""))

    This would look at both sheets only and if it can't find anything for the ID, return a blank.

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    See if this will work for you:

    =IFERROR(IF(ISBLANK(Task@row), "", IF(COUNTIFS({Master Range 2}, [Task]@row) > 0, INDEX({Master Range 1}, MATCH(Task@row, {Master Range 2}, 0)), INDEX({Slave List Range 1}, MATCH(Task@row, {Slave List Range 2}, 0)))), "")

  • CarlyS
    CarlyS ✭✭
    edited 11/06/23
    Options

    Thanks Carson Penticuff, but unfortunately, that did not work.

    The command is ok and does not give a syntax error (unlike my previous attempts!) and does return the value from the first sheet (Master) but does not return the value if I use an ID from the other sheet (slave).

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @CarlyS,

    Something like this?

    =IF(Title@row = "", IFERROR(INDEX({Sheet 1 Title}, MATCH(Task@row, {Sheet 1 ID}, 0)), IFERROR(INDEX({Sheet 2 Title}, MATCH(Task@row, {Sheet 2 ID}, 0)), "")), Title@row)

    If your Task name is blank, check the first sheet for the Task name by matching ID number.

    Example output:


    Sheet 1 is:

    Sheet 2 is similar, but with ID 2 & Title B.

    In the event your ID is not on any sheet or manually input then the title will come up blank (as seen in 4), though you can change this to something else (e.g. "Not found", by altering the the last "" in the formula to your chosen output).

    Hope this helps - if I've misunderstood something or you've any problems/questions then just post. 🙂

  • CarlyS
    CarlyS ✭✭
    Options

    Thanks, @Nick Korna that is exactly what I am trying to do, but without the title (middle) column in your first picture, just the task and title lookup column with the formula.

    I've used your code like this:

    =IF(Title@row = "", IFERROR(INDEX({sheet1 Range 1}, MATCH(Task@row, {sheet1 Range 2}, 0)), IFERROR(INDEX({sheet2 Range 1}, MATCH(Task@row, {sheet2 Range 2}, 0)), "")), Title@row)

    The ranges are mapped to the correct column on the other sheets (Title then ID)

    But I am now getting #CIRCULAR REFERENCE as the result.

    Please advise. Thanks

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Yes - you're telling the formula to look at itself here and it can't do this.

    If you just want it to check the 2 sheets without anything else, then you would use:

    =IFERROR(INDEX({Sheet 1 Title}, MATCH(Task@row, {Sheet 1 ID}, 0)), IFERROR(INDEX({Sheet 2 Title}, MATCH(Task@row, {Sheet 2 ID}, 0)), ""))

    This would look at both sheets only and if it can't find anything for the ID, return a blank.

  • CarlyS
    CarlyS ✭✭
    Options

    Thank you so much @Nick Korna. That did the trick and worked perfectly, I am now seeing the behavior I wanted.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!