Google Sheets – Compare Single Cell to Multiple Values

formulasgoogle sheets

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.

Best Answer

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.

Related Solutions

Google Sheets – How to Refresh a Cell

Add the following line of code:

SpreadsheetApp.flush()

See reference: flush

Google Sheets – Multiple Substitutions in a Single Text in Google Sheets

As discussed in the thread you linked to in comments, the formula solution is quite convoluted and potentially very slow, if there are multiple instances to be replaced in the string. However with your qualification:

And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.

I believe the solution becomes a little more straight forward (and reasonably efficient, I would say):

=IFERROR(REGEXREPLACE(A2;JOIN("|";Lookup!$A$2:$A$5);VLOOKUP(REGEXEXTRACT(A2;JOIN("|";Lookup!$A$2:$A$5));Lookup!$A$2:$B$5;2;0)&"");A2)

which has the added advantage of being able to be easily converted into an array formula that will populate down the column:

=ArrayFormula(IFERROR(REGEXREPLACE(A2:A;JOIN("|";Lookup!A2:A5);VLOOKUP(REGEXEXTRACT(A2:A;JOIN("|";Lookup!A2:A5));Lookup!A2:B5;2*SIGN(ROW(A2:A));0)&"");A2:A))

I have also entered a custom function solution:

function MSUBSTITUTE(text, subTable)
{
  var searchArray = [], subArray = [];
  for (var i = 0, length = subTable.length; i < length; i++)
  {
    if (subTable[i][0])
    {
      searchArray.push(subTable[i][0]);
      subArray.push(subTable[i][1]);
    }
  }
  var re = new RegExp(searchArray.join('|'), 'g');
  return text.replace(re, function (match) {return subArray[searchArray.indexOf(match)];});
}

=MSUBSTITUTE(A2;Lookup!$A$2:$B$5)

It would be fairly trivial to convert this to an auto-populating array formula, as well.

Best Answer

Related Solutions

Google Sheets – How to Refresh a Cell

Google Sheets – Multiple Substitutions in a Single Text in Google Sheets

Related Topic