Adding an OR to an IFERROR

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!

Find more posts tagged with

Accepted answers

Nick Korna

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.

All comments

Carson Penticuff

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

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

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

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

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

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

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.