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).

Answer 1

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.

Answer 2

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 , , <LOTS OF COMMAS> , 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!