CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Digital Farmer
Digital Farmer

Reputation: 2107

Simplify formula with multiple IFS from the same column of data

All IFS are from the same range CO4:CO53, changing only the value to be searched, considering that there is only one range to search, is there any way to simplify it so that it doesn't get so big?

It is noteworthy that all must be changed to the same value if found.

=ARRAYFORMULA(
              IFERROR(
                      IFS(
                          CO4:CO53="Out","",
                          CO4:CO53="Mid","",
                          CO4:CO53="Early","",
                          CO4:CO53="Late","",
                          CO4:CO53="Red","",
                          CO4:CO53="Yellow","",
                          CO4:CO53="1","",
                          CO4:CO53="2","",
                          CO4:CO53="3","",
                          CO4:CO53="Disciplinary","",
                          CO4:CO53=1,"",
                          CO4:CO53=2,"",
                          CO4:CO53=3,""
                         ),
                      'Copy of Top Plantel A'!A2:A51
                     )
             )

Upvotes: 0

Views: 47

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

See my comment attached to the original post.

However, given only what I can see, you can try this method:

=ArrayFormula(IF(REGEXMATCH(CO4:CO53&"","^(Out|Mid|Early|Late|Red|Yellow|1|2|3|Disciplinary)$"),,'Copy of Top Plantel A'!A2:A51))

REGEXMATCH returns TRUE (if a match is found) or FALSE if not.

The pipe symbol acts as OR.

A space is appended to CO4:CO53 to convert the numbers 1, 2, 3 to strings (necessary for use with REGEX-type functions).

If the formula as written does not work as expected, please do share a link to the spreadsheet (or a copy of it), with permissions set to "Anyone with the link..." and "Editor."

Upvotes: 1

Related Questions