19

I am attempting to change the value of a cell in Google Sheets based on the value of an adjacent cell. This adjacent cell gets its value from the colour of the cell adjacent to it using the formula from this Quora answer. So the value of the cell should change based on the hex code which is obtained from the colour of the first cell. There are four possible hex values at the moment so I was trying to put four different IF statements in the same cell, so its value is changed based on this.

My first attempt led to a formula parse error:

=if((J2="#00ff00","Read"), if(j2="#ff0000", "Unread"), if(j2="#ff9900","In Progress"), if(j2="#000000", "Not Applicable"))

It is clear you cannot simply separate if statements with commas. Based on this Stack Overflow question I tried this code, with nested IF statements:

=if((J2="#00ff00","Read", if(j2="#ff0000", "Unread", if(j2="#ff9900","In Progress",if(j2="#000000", "Not Applicable")))))

This also leads to a formula parse error.

Is there any way I can simply include multiple IF statements in the same cell which will work for my code?

Improve this question
3

5 Answers 5

Reset to default
22

Use lookup instead of if:

=lookup(B7,
       {"#000000","#00ff00","#ff0000","#ff9900"},
       {"Not applicable", "Read","Unread","In Progress"}
  )

Note the second parameter must be a sorted list.

Improve this answer
3
  • 1
    Note that this doesn't work if the items in the 3rd argument are expression
    – Christiaan Westerbeek
    Commented Apr 7, 2017 at 5:11
  • 1
    Note that the first array (the "key" array) has to be sorted (it worked when mine was alphabetically sorted ascendingly) for it to work as intended.
    – fgblomqvist
    Commented Nov 19, 2017 at 21:26
  • @ChristiaanWesterbeek, is there a way around this, or do you just have to use nested ifs?
    – Andrew
    Commented Mar 29, 2018 at 3:34
23

Short answer

The problem in the examples provided are the parenthesis. Apply them properly.

Explanation

IF() function should have two parameters and optionally a third one.

IF(logical_expression, value_if_true, value_if_false)

The specific problem with

  • the first example provided is that the outer IF() has too many parameters.
  • the second example is that the logical_expression of the outer IF() do not return TRUE or FALSE

In Google Sheets the functions parameters are separated by commas (or semicolons if your spreadsheet uses comma as decimal separator). When parenthesis are used to enclose several operations and functions inside a function they are considered as a parameter of the function that contains them.

A common practice is to put the inner IF() as the value_if_false, but it could be done in many ways. Adding IF() inside another other as value_if_true and value_if_false is called IF() logical test nesting or just IF() nesting.

Below is an example of a formula that have having three IF(), two of them used to determine the value_if_false of the parent IF(). A multi-line and vertical align of parenthesis style is applied for readability

 =IF(logical_expression, value_if_true, 
     IF(logical_expression, value_if_true, 
        IF(logical_expression, value_if_true, value_if_false
          )
       )
    )

The above style could be used in Google Sheets formula writing. I found it useful for formula debugging.

Reference

Improve this answer
3
  • 1
    Very useful, especially the formatted multiline sample - made it much easier to understand and implement.
    – Luke
    Commented Oct 2, 2019 at 3:24
  • 1
    Thank you... this helped me fix my problem.
    – Geert Schuring
    Commented Feb 13, 2020 at 15:54
  • 1
    You can also look at the IFS function: support.google.com/docs/answer/7014145?hl=en
    – PKHunter
    Commented Aug 6, 2020 at 17:46
5

I just found out how to combine if and or.

Example:

=IF(OR(B3>49,C3>49,D3>49),”Passed”,”Failed”)
=IF(AND(B3>49,C3>49,D3>49),”Passed”,”Failed”)
=IF(OR(AND(B3>49,C3>49),AND(B3>49,D3>49),AND(C3>49,D3>49)),”Won”,”Failed”)

So for your example:

= if(OR(J2="#00ff00",j2="#ff0000",j2="#ff9900"),"True","False")

Source: Combined Use of IF, AND, OR Logical Functions in Google Doc Spreadsheet.

Improve this answer
2

You can nest IF statements for example

If(if(if(when all 3 equal true)))

Would work

OR

you could use a nested IFAND for example

IF(AND(AND()))

Would also work.

Improve this answer
1
1

You have been offered theory and better approaches but to answer specifically:

Is there any way I can simply include multiple IF statements in the same cell which will work for my code?

please try:

=if(J2="#00ff00","Read", if(J2="#ff0000", "Unread", if(J2="#ff9900","In Progress",if(J2="#000000", "Not Applicable"))))

That is, your second attempt without the first opening parenthesis and without the last closing parenthesis.

Improve this answer