Forum Discussion

GeorgieAnne's avatar
GeorgieAnne
Iron Contributor
Jul 06, 2026

Can anyone help me with this SWITCH function

Hello Everyone, This SWITCH function is used to produce a number that then I can use to sort workers and their functions. =SWITCH(TRUE,     IF(AND(ISNUMBER(J2)=TRUE,M2="Working")=TRUE,5,0),     I...
developer
excel
Formulas and Functions
office 365
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jul 07, 2026

If your intent is:

  • 5/15/20/25 = task has started but not yet ended (J2 filled, K2 empty)
  • 500/515/520/525 = task has ended (K2 filled, regardless of J2)

Then you want to check K2 first, or make the J2 condition explicitly exclude K2:

=SWITCH(TRUE,

    AND(ISNUMBER(K2),M2="Working"),500,

    AND(ISNUMBER(J2),M2="Working"),5,

    AND(ISNUMBER(K2),M2="Special Project"),515,

    AND(ISNUMBER(J2),M2="Special Project"),15,

    AND(ISNUMBER(K2),M2="Stand By"),520,

    AND(ISNUMBER(J2),M2="Stand By"),20,

    AND(ISNUMBER(K2),M2="Special Project UFN"),525,

    AND(ISNUMBER(J2),M2="Special Project UFN"),25,

    50)

This way, if K2 has an end time, that takes priority and returns the "500-series" number, regardless of whether J2 also has a value. Only if K2 is not a number does it fall through to check J2.

Does that match what you're trying to track (started vs. finished), or is the distinction between J2 and K2 meant to represent something else?