Multiple IFS

JRose

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" )

J. Craig Williams

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