Using IF OR MATCH to Check for Value in Multiple Sheets
Hi,
I am trying to write a formula that will take a value from the sheet the formula is in and check for that value in other sheets. If the value is found in at least one other sheet, the formula should return "Y". If it is not found, it should return "N".
This is my current attempt, but this just returns "N" for everything.
=IF(OR(ISERROR(MATCH([Org Unit Code]@row, {Concourse_Assignments Range 1}, 0)), ISERROR(MATCH([Syllabus Assignments]@row, {Concourse_AssignmentEntries Range 1}, 0))), "N", "Y")
I am basing this formula off of the following. The below formula works, but it is only checking one sheet for a value instead of two (or more than two).
=IF(ISERROR(MATCH([Org Unit Code]@row, {Concourse_ContactInformation Range 1}, 0)), "N", "Y")
As soon as I try to add a secondary check to the formula, it breaks. I either get "N" for everything or "Unparseable" errors.
Any help would be greatly appreciated.
Note: I apologize if this is a duplicate post. I posted this question a short while ago, but when I came back to the site, it said I had no posts with my profile and I couldn't find it anywhere so I am not sure what happened.
Answers

Hi @cmilam,
I tested out your formula on my sheet and made one adjustment. Instead of using OR I switched it to AND. The reason why I did this is because if the value is not found in {Concourse_Assignments Range 1} but it is found in {Concourse_AssignmentEntries Range 1} then we want "Y", instead of "N".
Try this out:
=IF( AND( ISERROR(MATCH([ID]@row, {Sheet A Range}, 0)), ISERROR(MATCH([ID]@row, {Sheet B Range}, 0)) ), "N", "Y" )
The only time that we want "N" is if we got an error from both sheet's ranges.
Hope this helps!
SSFeatures  The browser extension that adds more features into SmartSheet.
 Automatic sorting, sorting with filters, saving sort settings
 Spell checking
 Report PDF generator that supports grouped and summarized reports

Hi @SSFeatures
Doesn't using AND check to see if the value is in BOTH of the sheets' ranges? AND typically means that both values have to return TRUE in order for the equation to return TRUE. Doesn't that logic apply to Smartsheet formulas as well?

Hi @cmilam,
Yes that's correct, that is how AND works. Just to be sure, this is the behavior that you mentioned that you wanted:
If the value is found in at least one other sheet, the formula should return "Y". If it is not found, it should return "N".
Remember that ISERROR will return TRUE if the value was not found in the sheet.
=IF( AND( ISERROR(MATCH([ID]@row, {Sheet A Range}, 0)), ISERROR(MATCH([ID]@row, {Sheet B Range}, 0)) ), "N", "Y" )
So with this formula, we will only return "N" if ISERROR returns true for both Sheet A and Sheet B. If ISERROR returns false for Sheet A then that means that the value was found in Sheet A, so the AND condition is false, and we return "Y". If ISERROR returns false for Sheet B then that means that the value was found in Sheet B, so the AND condition is false and we also return "Y" here.
Notice that the "N" comes before the "Y" in the formula. So "N" will only happen if the both conditions in the AND are true. This only happens if there was no match in both sheets.
Is this the logic that you wanted?
SSFeatures  The browser extension that adds more features into SmartSheet.
 Automatic sorting, sorting with filters, saving sort settings
 Spell checking
 Report PDF generator that supports grouped and summarized reports

@SSFeatures This does not satisfy the behavior I mentioned. "The value found in at least one other sheet" does not require it to be in both sheets. "At least one" other sheet implies that it only needs to be in one of the two sheets, not both.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!