0

In programming, I could use a switch/case statement to do something like this:

func animalNameFromType(animalType: Int) {
  switch animalType {
    case 1: return "dog"
    case 2: return "cat"
    case 3: return "bird"
  }
}

animalNameFromType(2) == "cat"

The equivalent in Google Sheets is the SWITCH function:

=SWITCH(<value>, 1, "dog", 2, "cat", 3, "bird")

However, in programming, I could also do the opposite and return the index of an item in a list:

["dog", "cat", "bird"].indexOf("cat") == 2

How do I do something similar in Google Sheets?

1

Since you are essentially using an ENUM in Google Sheets and the index is increasing by exactly one in each case, your best bet is to use arrays.

If you had a more complicated mapping between integers to strings, you should use the SWITCH function.

Here are examples in both directions (Int to String, and String to Int)...

Option 1: via arrays (preferred)

=Index({"dog", "cat", "bird"}, 1, <index>) // e.g. if <index> is 2, it'll return "cat"
=MATCH(<value>, {"dog", "cat", "bird"}, 0) // e.g. if <value> is "cat", it'll return 2

Option 2: via SWITCH function

=SWITCH(<index>, 1, "dog", 2, "cat", 3, "bird") // e.g. if <index> is 2, it'll return "cat"
=SWITCH(<value>, "dog", 1, "cat", 2, "bird", 3) // e.g. if <value> is "cat" it'll return 2
0

You can create a custom function to use Array.prototype.indexOf as it's shown in the question. Next is a simple implementation:

/**
 *
 * @param {Array} values
 * @param {String|Integer} searchElement
 * @customfunction
 */
function indexOf(values,searchElement){
  return values.indexOf(searchElement);
}

References

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.