Google-sheets – How to use nested IF statements in Google Sheets

google sheets

I am attempting to put the following flow into an equation in Google Sheets:

  • Compare string from a given cell (cell 1)
  • If the string is "A" evaluate a different cell (cell 2) under these conditions
  • If its not "A" and the string is "B" evaluate a different (cell 2) under these conditions
  • If its not "A" or "B" and the string is "C" evaluate a different (cell 2) under these conditions
    (the only possibilities are A, B, and C)

Here is what I've tried:

  1. =IFS((D3="A",IFS((E2<=11,"L1"),(E2<=12.1,"L2"),(E2<=14.7,"L3"),(E2>14.7,"L4"))),OR(D3="B",IFS((E2<=11.8,"L1")OR(E2<=13.7,"L2")OR(E2<=14.6,"L3")OR(E2>14.6,"L4"))),OR(D3="C",IFS((E2<=15.8,"L1"),(E2<=17.8,"L2"), (E2<=19.8,"L3"), (E2>19.8,"L4"))))
  2. =IFS((D3="A",(E2<=11,"L1"),OR(E2<=12.1,"L2"),OR(E2<=14.7,"L3"),OR(E2>14.7,"L4")),OR(D3="B",(E2<=11.8,"L1")OR(E2<=13.7,"L2")OR(E2<=14.6,"L3")OR(E2>14.6,"L4")),OR(D3="C",(E2<=15.8,"L1")OR(E2<=17.8,"L2")OR(E2<=19.8,"L3")OR(E2>19.8,"L4")))

This formula does all but the first step of verifying the first cell and evaluating based on the contents of that cell:

  • =IFS(E2<=12.1,"L1",E2<=14.6,"L2",E2<=17.3,"L3",E2>17.3,"L4")

Best Answer

=if(D3="A",if(E2<=11,"L1",if(E2<=12.1,"L2",if(E2<=14.7,"L3","L4"))), if(D3="B",if(E2<=11.8,"L1",if(E2<=13.7,"L2",if(E2<=14.6,"L3","L4"))), if(D3="C",if(E2<=15.8,"L1",if(E2<=17.8,"L2",if(E2<=19.8,"L3","L4"))),"Not A, B or C")))

The logic here is:

  • IF D3="A", then
    • IF E2<=11, "L1", ELSE
    • IF E2<=12.1, "L2", ELSE
    • IF E2<=14.7, "L3",
    • ELSE "L4"
  • ELSE IF D3="B", then
    • IF E2<=11.8, "L1", ELSE
    • IF E2<=13.7, "L2", ELSE
    • IF E2<=14.6, "L3", ELSE "L4"
  • ELSE IF D3="C", then
    • IF E2<=15.8, "L1", ELSE
    • IF E2<=17.8, "L2", ELSE
    • IF E2<=19.8, "L3", ELSE "L4"
  • ELSE D3 doesn't contain "A", "B" or "C"
Related Topic