SOLVED

SWITCH function

%3CLINGO-SUB%20id%3D%22lingo-sub-3370749%22%20slang%3D%22en-US%22%3ESWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370749%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20Excel%202016%20does%20anyone%20know%20why%20the%20SWITCH%20function%20isn't%20available%3F%20Is%20there%20any%20other%20function%20I%20can%20use%20instead%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3370749%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370914%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389562%22%20target%3D%22_blank%22%3E%40majormajor87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1652451936784.png%22%20style%3D%22width%3A%20437px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371464i9C5201529F1033E8%2Fimage-dimensions%2F437x177%3Fv%3Dv2%22%20width%3D%22437%22%20height%3D%22177%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1652451936784.png%22%20alt%3D%22Riny_van_Eekelen_0-1652451936784.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370957%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370957%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389562%22%20target%3D%22_blank%22%3E%40majormajor87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESWITCH%20is%20available%20in%20Excel%202019%20and%20later.%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20IFS%20instead.%20For%20example%3C%2FP%3E%0A%3CP%3E%3DSWITCH(A1%2C1%2C%22one%22%2C2%2C%22two%22%2C3%2C%22three%22%2C%22none%22)%3C%2FP%3E%0A%3CP%3Ecan%20be%20replaced%20with%3C%2FP%3E%0A%3CP%3E%3DIFS(A1%3D1%2C%22one%22%2CA1%3D2%2C%22two%22%2CA1%3D3%2C%22three%22%2CTRUE%2C%22none%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370981%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370981%22%20slang%3D%22en-US%22%3EIFS%20also%20isn't%20available%20on%20this%20version%20of%20excel%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3371194%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3371194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389562%22%20target%3D%22_blank%22%3E%40majormajor87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOops%2C%20sorry.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(type%3D%22%22%2C%22%22%2CIF(type%3D%22W%22%2C1%2CIF(type%3D%22SW%22%2C2%2CIF(type%3D%22T%22%2C3%2CIF(type%3D%22M%22%2C4%2C-1)))))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20use%20a%20LOOKUP%20or%20VLOOKUP%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3371073%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3371073%22%20slang%3D%22en-US%22%3EWhat%20can%20I%20replace%20SWITCH%20and%20IFS%20with%20to%20make%20this%20formula%20work%3F%3CBR%20%2F%3E%3DIF(type%26lt%3B%26gt%3B%22%22%2CSWITCH(type%2C%22W%22%2C1%2C%22SW%22%2C2%2C%22T%22%2C3%2C%22M%22%2C4)%2C-1%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3371621%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3371621%22%20slang%3D%22en-US%22%3EThis%20also%20didn't%20work.%20It%20said%20there%20are%20too%20many%20arguments%3F%20I'm%20really%20struggling%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20this%20be%20because%20the%20cells%20already%20have%20complicated%20conditional%20formatting%20arguments%20already%3F%20I'm%20following%20a%20guide%20but%20without%20SWITCH%20or%20an%20equivalent%20all%20my%20hours%20of%20work%20will%20be%20useless%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3372070%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3372070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389562%22%20target%3D%22_blank%22%3E%40majormajor87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20should%20work.%20Are%20you%20sure%20you%20copied%20it%20correctly%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1431.png%22%20style%3D%22width%3A%20741px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371500i58E36ACE75DB75D5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1431.png%22%20alt%3D%22S1431.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3398049%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3398049%22%20slang%3D%22en-US%22%3E%3CP%3Eyes%20it%20worked%20thank%20you%20so%20much%20for%20your%20help%20I%20really%20appreciate%20it%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm using Excel 2016 does anyone know why the SWITCH function isn't available? Is there any other function I can use instead?

8 Replies

@majormajor87 

SWITCH is available in Excel 2019 and later.

You can use IFS instead. For example

=SWITCH(A1,1,"one",2,"two",3,"three","none")

can be replaced with

=IFS(A1=1,"one",A1=2,"two",A1=3,"three",TRUE,"none")

IFS also isn't available on this version of excel?
What can I replace SWITCH and IFS with to make this formula work?
=IF(type<>"",SWITCH(type,"W",1,"SW",2,"T",3,"M",4),-1

If I
best response confirmed by majormajor87 (Occasional Contributor)
Solution

@majormajor87 

Oops, sorry.

 

=IF(type="","",IF(type="W",1,IF(type="SW",2,IF(type="T",3,IF(type="M",4,-1)))))

 

Or use a LOOKUP or VLOOKUP formula.

This also didn't work. It said there are too many arguments? I'm really struggling

Could this be because the cells already have complicated conditional formatting arguments already? I'm following a guide but without SWITCH or an equivalent all my hours of work will be useless

@majormajor87 

It should work. Are you sure you copied it correctly?

S1431.png

yes it worked thank you so much for your help I really appreciate it  @Hans Vogelaar