Using the Switch function to return Employment tenure

[bearcub]

I am trying to learn the new excel functions and one them I don't totally understand is the Switch function.

We want to classify the length of time a rep has been with the company by putting them in the follow buckets:

Here is the formula we are presently using to put them in those buckets and I thought their should a more efficient way of doing this. If there is, can I use an array function to replace the following nested IF formula:

IF(J3>4,"4+ YRS",IF(J3<1,"0-1 YRS",IF(AND(J3>1,J3<2),"1-2 YRS","2-4 YRS")))

This is the result section. Is there an array formula or more efficient function to use other than the IF function

 

[SunnyAlv]

try

with SWITCH

=SWITCH(TRUE,J3>4,"4+ YRS",J3<1,"0-1 YRS",AND(J3>1,J3<2),"1-2 YRS","2-4 YRS")

with IFS

=IFERROR(IFS(J3>4,"4+ YRS",J3<1,"0-1 YRS",AND(J3>1,J3<2),"1-2 YRS"),"2-4 YRS")

 

[SunnyAlv]

to avoid IF / Switch Funtions

try Lookup, but its need table ( Cell S3:T7 )

=LOOKUP(J3,$S$3:$T$7)

 

[Alex Blakenburg]

When using grouping logic, I am also a fan of using a lookup table as in @SunnyAlv's 2nd example, although I probably would use VLookup.
If you are using "If statement" style logic, if you put the arguments in descending order there should be no reason to use AND (for between) in the logic and just rely on the conditions being evaluate from left to right. eg

=IF(J3>=4,"4+ YRS",IF(J3>=2,"2-4 YRS",IF(J3>=1,"1-2 YRS","0-1 YRS")))

=IFS(J3>=4,"4+ YRS",J3>=2,"2-4 YRS",J3>=1,"1-2 YRS",TRUE,"0-1 YRS")

Note: I have used >=, swap that for > depending on which bucket you want the cutover numbers 1,2,4 to fall into.
Although its possible as Sunny has demonstrated you won't see too many people using Switch for this type of scenario.
Switch is aimed at exact matches, so if you want to use ">" you using a workaround when you are using Switch to make it work.
The comparison is talked about here towards the bottom:

IFS Function

The Excel IFS function can run multiple tests and return a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas.

exceljet.net

The Switch function is described here:

SWITCH Function

The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to the first match found. When no match is found, SWITCH can return an optional default value.

exceljet.net

 

[bearcub]

Thank you both for the formulas - both work great. You were right about replacing the equal sign with >=.

 

[Alex Blakenburg]

You're welcome. Glad we could help.