3

I have a cell in a spreadsheet that can have virtually any value. However, if that cell has one of four different values, I want a different cell to display certain text.

Right now I have this:

=IF(OR(A1="Cyan",A1="Magenta",A1="Yello",A1="Key"),"CMYK","Other")

Is there a simpler (less verbose) way to compare A1 to those values?

I looked at IFS() and SWITCH(), but they don't seem to be any less verbose.

  • This looks like a Code Review question :) – Rubén Jun 4 '17 at 1:56
  • By the way, what do you mean by "less verbose"? Formula length, semantically complexity? – Rubén Jun 4 '17 at 2:07
  • Are custom functions allowed? – Rubén Jun 4 '17 at 2:27
  • I suppose a custom function is an option, but it seems to be a lot of bother. I'm just looking for something a little simpler than what I've got. It's possible that I've got it about as simple as it gets. – ale Jun 4 '17 at 3:40
3

For the specific case on the question it looks to me that the formula included already has the simpler formula

=IF(OR(A1="Cyan",A1="Magenta",A1="Yello",A1="Key"),"CMYK","Other")

as the alternatives are more complex in one or another way.

IFS and SWITCH are pretty new Google Sheets built-in functions. Side-note: their help articles are not translated yet to Spanish. If they were found to not be any less verbose, then the following examples of alternatives will not found any less verbose either.

  • Using MATCH returns a number or an error and it requires that its second argument to be an array or a reference to a range. If it doesn't found the input value, it will return an error so it use for this case implies a way to catch the error.
  • Using VLOOKUP also requires that its second argument to be an array or a reference to a range. Also if it doesn't found the input value, it will return an error so it use for this case implies a way to catch the error.
  • The use of REGEXMATCH to be considered "simpler" implies that the formula writer feels comfortable writing regular expressions and that simpler doesn't mean shorter, as the resulting formula will be larger than the above formula.
| improve this answer | |
0
=if(isna(match(A1, {"Cyan", "Magenta", "Yello", "Key"})), "Other", "CMYK")

it's more concise if you keep the list of acceptable values in a row or column somewhere else, e.g. B1:B4

=if(isna(match(A1, B1:B4)), "Other", "CMYK")
| improve this answer | |
  • I don't understand your question, "Other" is an outcome of the IF, not one of the accepted values. And I think it's not about the number of characters, but about syntactic complexity -- writing multiple conditions feels more complex than comparing them all in a single function. – ttarchala Jun 3 '17 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.