Case Function Equivalent in Excel

Question

I have an interesting challenge - I need to run a check on the following data in Excel       A  -   B  -   C  -  D      ------ ------ ------ ------     36      0      0      x        0     600    700     x                                    You ll have to excuse my wonderfully bad ASCII art  So I need the D column  x  to run a check against the adjacent cells  then convert the values if necessary  Here s the criteria   If column B is greater than 0  everything works great and I can get coffee  If it doesn t meet that requirement  then I need to convert A1 according to a table - for example  32   1420 and place into D  Unfortunately  there is no relationship between A and what it needs to convert to  so creating a calculation is out of the question   A case or switch statement would be perfect in this scenario  but I don t think it is a native function in Excel  I also think it would be kind of crazy to chain a bunch of  IF   statements together  which I did about four times before deciding it was a bad idea  story of my life

User · Answer

The Switch function is now available  in Excel 2016   Office 365       SWITCH expression  value1  result1   default or value2  result2      default or value3  result3    example   SWITCH A1 0  FALSE  -1  TRUE   Maybe       Microsoft -Office Support  Note  MS has updated that page to only document the behavior of Excel 2019  Eventually  they will probably remove references to 2019 as well    To see what the page looked like in 2016  use the wayback machine  https   web archive org web 20161010180642 https   support office com en-us article SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e

User · Answer

Try this     IF B1 gt  0  B1  OFFSET  X 1  MATCH B1   X  X  Z  - 1  Y    WHERE   X   The columns you are indexing into  Y   The number of columns to the left  -Y  or right  Y  of the indexed column to get the value you are looking for Z   0 if exact-match  if you want to handle errors

User · Answer

If your using Office 2016 or later  or Office 365  there is a new function that acts similarly to a CASE function called IFS  Here s the description of the function from Microsoft s documentation   The IFS function checks whether one or more conditions are met  and returns a value that corresponds to the first TRUE condition  IFS can take the place of multiple nested IF statements  and is much easier to read with multiple conditions   An example of usage follows   IFS A2 gt 89  quot A quot  A2 gt 79  quot B quot  A2 gt 69  quot C quot  A2 gt 59  quot D quot  TRUE  quot F quot    You can even specify a default result   To specify a default result  enter TRUE for your final logical test argument  If none of the other conditions are met  the corresponding value will be returned   The default result feature is included in the example shown above  You can read more about it on Microsoft s Support Documentation

User · Answer

If you don t have a SWITCH statement in your Excel version  pre-Excel-2016   here s a VBA implementation for it   Public Function SWITCH ParamArray args   As Variant  As Variant     Dim i As Integer     Dim val As Variant     Dim tmp As Variant      If   UBound args  - LBound args     0  Or    UBound args  - LBound args   Mod 2   0   Then         Error 450        Invalid arguments     Else         val   args LBound args           i   LBound args    1         tmp   args UBound args            While  i  lt  UBound args               If val   args i  Then                 tmp   args i   1              End If             i   i   2         Wend     End If      SWITCH   tmp End Function   It works exactly like expected  a drop-in replacement for example for Google Spreadsheet s SWITCH function   Syntax    SWITCH selector   keyN  valueN        defaultvalue    where   selector is any expression that is compared to keys key1  key2      are expressions that are compared to the selector value1  value2      are values that are selected if the selector equals to the corresponding key  only  defaultvalue is used if no key matches the selector   Examples    SWITCH  a                             returns      SWITCH  a   a   1                     returns  1   SWITCH  x   a   1                     returns      SWITCH  b   a   1   b  TRUE           returns TRUE  SWITCH 7 7 1 7 2 0                    returns 2  SWITCH  a   a   1                     returns  VALUE    To use it  open your Excel  go to Develpment tools tab  click Visual Basic  rightclick on ThisWorkbook  choose Insert  then Module  finally copy the code into the editor  You have to save as a macro-friendly Excel workbook  xlsm

User · Answer

Microsoft replace SWITCH  IFS and IFVALUES with CHOOSE only function     CHOOSE  L 1  index 1   Index 2   Index 3

User · Answer

Even if old  this seems to be a popular questions  so I ll post another solution  which I think is very elegant   http   fiveminutelessons com learn-microsoft-excel using-multiple-if-statements-excel  It s elegant because it uses just the IF function   Basically  it boils down to this   if condition  choose use a value from the table  if condition  choose use another value from the table     And so on  Works beautifully  even better than HLOOKUP or VLOOOKUP  but     Be warned - there is a limit to the number of nested if statements excel can handle

User · Answer

I understand that this is a response to an old post-   I like the If   function combined with Index   Match      IF B2 gt 0  x  INDEX  H 2  I 9 MATCH A2  H 2  H 9 0  2     The if function compare what is in column b and if it is greater than 0  it returns x  if not it uses the array  table of information  identified by the Index   function and selected by Match   to return the value that a corresponds to   The Index array has the absolute location set  H 2  I 9  the dollar signs  so that the place it points to will not change as the formula is copied  The row with the value that you want returned is identified by the Match   function  Match   has the added value of not needing a sorted list to look through that Vlookup   requires  Match   can find the value with a value  1 less than  0 exact  -1 greater than  I put a zero in after the absolute Match   array  H 2  H 9 to find the exact match  For the column that value of the Index   array that one would like returned is entered  I entered a 2 because in my array the return value was in the second column  Below my index array looked like this   32   1420  36   1650  40   1790  44   1860  55   2010   The value in your  a  column to search for in the list is in the first column in my example and the corresponding value that is to be return is to the right  The look up reference table can be on any tab in the work book - or even in another file  -Book2 is the file name  and Sheet2 is the  other tab  name    IF B2 gt 0  x  INDEX  Book2 Sheet2  A 1  B 8 MATCH A2  Book2 Sheet2  A 1  A 8 0  2     If you do not want x return when the value of b is greater than zero delete the x for a  blank  null equivalent or maybe put a 0 - not sure what you would want there   Below is beginning of the function with the x deleted    IF B2 gt 0    INDEX

User · Answer

I used this solution to convert single letter color codes into their descriptions    CHOOSE FIND H5  GYR    Good   OK   Bad     You basically look up the element you re trying to decode in the array  then use CHOOSE   to pick the associated item  It s a little more compact than building a table for VLOOKUP

User · Answer

Sounds like a job for VLOOKUP   You can put your 32 -  1420 type mappings in a couple of columns somewhere  then use the VLOOKUP function to perform the lookup

User · Answer

I know it a little late to answer but I think this short video will help you a lot   http   www xlninja com 2012 07 25 excel-choose-function-explained   Essentially it is using the choose function  He explains it very well in the video so I ll let do it instead of typing 20 pages   Another video of his explains how to use data validation to populate a drop down which you can select from a limited range   http   www xlninja com 2012 08 13 excel-data-validation-using-dependent-lists   You could combine the two and use the value in the drop down as your index to the choose function  While he did not show how to combine them  I m sure you could figure it out as his videos are good  If you have trouble  let me know and I ll update my answer to show you

User · Answer

Recently I unfortunately had to work with Excel 2010 again for a while and I missed the SWITCH function a lot  I came up with the following to try to minimize my pain    CHOOSE SUM  A1   a   b   c    ROW INDIRECT 1 amp     amp 3    1 2 3  CTRL SHIFT ENTER   where A1 is where your condition lies  it could be a formula  whatever   The good thing is that we just have to provide the condition once  just like SWITCH  and the cases  in this example  a b c  and results  in this example  1 2 3  are ordered  which makes it easy to reason about   Here is how it works    Cond   c1   c2       cn   returns a N-vector of TRUE or FALSE  with behaves like 1s and 0s  ROW INDIRECT 1 amp     amp n   returns a N-vector of ordered numbers  1 2 3     n The multiplication of both vectors will return lots of zeros and a number  position  where the condition was matched SUM just transforms this vector with zeros and a position into just a single number  which CHOOSE then can use If you want to add another condition  just remember to increment the last number inside INDIRECT If you want an ELSE case  just wrap it inside an IFERROR formula The formula will not behave properly if you provide the same condition more than once  but I guess nobody would want to do that anyway

User · Answer

Without reference to the original problem  which I suspect is long since solved   I very recently discovered a neat trick that makes the Choose function work exactly like a select case statement without any need to modify data  There s only one catch  only one of your choose conditions can be true at any one time   The syntax is as follows   CHOOSE        1    CONDITION 1      2    CONDITION 2            N    CONDITION N        RESULT 1  RESULT 2        RESULT N     On the assumption that only one of the conditions 1 to N will be true  everything else is 0  meaning the numeric value will correspond to the appropriate result   If you are not 100  certain that all conditions are mutually exclusive  you might prefer something like   CHOOSE       1   TEST1     2   TEST2     4   TEST3     8   TEST4       2 N   TESTN      OUT1  OUT2    OUT3        OUT4      lt LOTS OF COMMAS gt    OUT5     That said  if Excel has an upper limit on the number of arguments a function can take  you d hit it pretty quickly   Honestly  can t believe it s taken me years to work it out  but I haven t seen it before  so figured I d leave it here to help others   EDIT  Per comment below from  aTrusty  Silly numbers of commas can be eliminated  and as a result  the choose statement would work for up to 254 cases  by using a formula of the following form   CHOOSE      1   LOG 1    2 TEST1     4 TEST2     8 TEST3     16 TEST4  2        OTHERWISE  RESULT1  RESULT2  RESULT3  RESULT4     Note the second argument to the LOG clause  which puts it in base 2 and makes the whole thing work   Edit  Per David s answer  there s now an actual switch statement if you re lucky enough to be working on office 2016  Aside from difficulty in reading  this also means you get the efficiency of switch  not just the behaviour

[excel] Case Function Equivalent in Excel

Examples related to excel

Examples related to worksheet-function