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!
Best Answer
-
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
-
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)))), "")
-
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).
-
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. 🙂
-
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
-
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.
-
Thank you so much @Nick Korna. That did the trick and worked perfectly, I am now seeing the behavior I wanted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!