Multiple IFS
Hi, this should be relatively easy I think, but I'm struggling. I have the following formula (pasted below these comments) functioning to test whether a certain value exists (IFC) within a range on another sheet, and then returning "" if no, or that same value ("IFC") if yes.
I want to continue and test the same range for IBC and return the same "" or IBC, and then test the same range for BC and return the same "" or BC, and so on.
How do I please combine those in the formula? Many thanks.
=IF( COUNTA( IFERROR( FILTER( DeLand_2018!R1:R2000, DeLand_2018!R1:R2000="IFC" ) ) ) , "" , "IFC" )
Comments
-
1. Your x-sheet references are wrong. That's Excel/OpenOffice syntax.
The x-sheet references will have curly braces around them.
https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets
2. You don't want to use COUNT (COUNTA is not a Smartsheet function)
Try MATCH instead
https://help.smartsheet.com/function/match
The doc does not say it, but MATCH() returns #NO MATCH when it does not find the search value.
=IFERROR(MATCH("IFC",{x-sheet range},0),"IFC",IFERROR(MATCH("IBC", ...and so on as deep as you want)
...
=IFERROR(MATCH(---arguments),"value if found", "value if not found")
where "value if not found" is replaced by that whole IFERROR() formula again.
Craig
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.3K Get Help
- 392 Global Discussions
- 213 Industry Talk
- 447 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!