CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
rhlchd
rhlchd

Reputation: 31

Is there something equivalent to switch case in microsoft excel?

I have to prepare an inventory sheet of my products. So I have three sizes, Small, Medium and Large. The details we receive from our supplier consists of a column consisting of abbreviated sizes S/M/L. But I prepare my version of the sheet with Small/Medium/Large.

So how do I simply check if:

case "S": Small
case "M": Medium
case "L": "Large"
default

Upvotes: 1

Views: 7332

Answers (6)

Alex
Alex

Reputation: 1101

The following is the way to do it if you have Excel 2010:

=IFERROR(VLOOKUP(Target, {"S","Small"; "M","Medium"; "L","Large"}, 2, FALSE), "else case")

Essentially all it does is, it finds the Target value in the first column of the array, and the 2 tells it to return the second column of the corresponding row. The FALSE argument tells it not to return anything but an exact match (otherwise, the entries "S","M","L" have to be sorted in alphabetical order for this to work), and the case when there is no exact match is handled by the IFERROR.

Turns out it's much simpler than I expected. Just have to call the right function with the right arguments. In fact, it is so simple that I considered not posting it at all -- but then I realized that none of the other solutions work in Excel 2010; the best is @FRocha's answer which works starting with Excel 2013.

Upvotes: 1

Vilas
Vilas

Reputation: 19

Excel introduced a new SWITCH function that basically is of the form =SWITCH(Expression, value1, result1, value2, result2.....). It should help.

Upvotes: 1

FRocha
FRocha

Reputation: 960

Since Excel 2016, this would be solved easily with SWITCH. But recently I had to work with Excel 2010 again for a while and there is no SWITCH function there. Using your example, here is a solution that does not use SWITCH:

=IFERROR(CHOOSE(SUM((A1={"S";"M";"L"})*ROW(INDIRECT(1&":"&3))),"SMALL","MEDIUM","LARGE"),"else case")

Since it is an array formula, you have to press CTRL+SHIFT+ENTER to make it work.

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&":"&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
  • 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

Upvotes: 1

Pit J
Pit J

Reputation: 179

Microsoft replace SWITCH, IFS and IFVALUES with CHOOSE only function. =CHOOSE($L$1,"index_1","Index_2","Index_3")

L1 must be equal to some index number part of series.

Upvotes: 0

pnuts
pnuts

Reputation: 59485

Select the relevant column (here A assumed) and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=A1="S"

Format..., Number, Category: Custom, Type: @"mall", OK, OK.

Then add rules in a similar way for "M" and "L".

Upvotes: 0

TheSilkCode
TheSilkCode

Reputation: 366

Select Case sizeString
    Case Is = "S"
        Size = "Small"
    Case Is = "M"
        Size = "Medium"
    Case Is = "L"
        Size = "Large"
    Case Else
        MsgBox ("There was an error with sizeString- did not contain an expected value.")
End Select

-TheSilkCode

Upvotes: 0

Related Questions