How To Use Boolean Logic With Strings To Replace Multiple Nested If Statements In a Google Sheets Formula?

user_10789707 0
Viewed 257

I've just found about the very handy use of boolean logic to shorten/replace multiple Nested If Statement from this question (4th answer):

Is there a way to shorten multiple if statements?

With this given example as substitute for multiple nested IF statements:

Multiple Nested IF Statements version:

= IF(E3 = "red", 100, IF(E3 = "blue", 200, IF(E3 = "green", 300, IF(E3 = "orange", 400, 500))))

Vs

Boolean Logic version:

= (E3 = "red") * 100 + (E3 = "blue") * 200 + (E3 = "green") * 300 + (E3 = "orange") * 400 + (E3 = "purple") * 500

That works well when returning a number is intended.

If true, the following returns 100 or 200.

=(REGEXMATCH(B19,F2)) * 100 + (REGEXMATCH(B19,F40)) * 200

But I get MULTIPLY or ADD errors when trying to return strings.

For example, with the 2 strings "Time" in cell F2 and "Test" in cell F40:

=(REGEXMATCH(B19,F2)) * F2 + (REGEXMATCH(B19,F40)) * F40

returns this error:

Function MULTIPLY parameter 2 expects number values. But 'Test' is a text and cannot be coerced to a number.

=(REGEXMATCH(B19,F2)) & F2 + (REGEXMATCH(B19,F40)) & F40

returns this error:

Function ADD parameter 1 expects number values. But 'Test' is a text and cannot be coerced to a number.

=(REGEXMATCH(B19,F2)) & F2 & (REGEXMATCH(B19,F40)) & F40

returns:

FALSETimeTRUETest

My question:

Is there a way to use Boolean logic with strings? If possible as concise as the way with numbers?

What operator is available for strings if there are some?

Many thanks for your help and insights!

google-sheets-formula google-sheets boolean-logic
1 Answers

The reason that the boolean solution is working with (REGEXMATCH(B19,F2)) * F2 when F2 is a number is that the formula translates TRUE|FALSE * F2, and sheets automatically converts TRUE|FALSE to 1|0 respectively if it detects that what you are multiplying/adding is a number since it is supported.

When F2 is a string, it doesn't support multiplying/adding a string and a number thus causes your issue.

What you can use is a simpler version of IF which doesn't need nesting, looks and functions like what you are doing. This is IFS.

Your formula:

=((REGEXMATCH(B19,F2)) * F2) + ((REGEXMATCH(B19,F40)) * F40)

Turns into:

=IFS(REGEXMATCH(B19,F2), F2, REGEXMATCH(B19,F40), F40)

The above solution can also be used on numbers as well.

Reference:

user_161186860
Related