Excel Select Case?

O.K., there's no way at all to do exactly what you want. You can't use anything other than Excel syntax within a formula, so stuff like 'A1 = 1 to 9' is just impossible.

You could write a pretty elaborate VBA routine that took strings or something and parsed them, but that really amounts to designing and implementing a complete little language. And your "code" wouldn't play well with Excel. For example, if you called something like

=cases("{A1="""",""there is nothing""},{else,A1}") 

(note the escaped quotes), Excel wouldn't update your A1 reference when it moved or the formula got copied. So let's discard the whole "syntax" option.

However, it turns out you can get much of the behavior I think you actually want with regular Excel formulas plus one tiny VBA UDF. First the UDF:

Public Function arr(ParamArray args()) arr = args End Function 

This lets us create an array from a set of arguments. Since the arguments can be expressions instead of just constants, we can call it from a formula like this:

=arr(A1=42, A1=99) 

and get back an array of boolean values.

With that small UDF, you can now use regular formulas to "select cases". They would look like this:

=CHOOSE(MATCH(TRUE, arr(A1>5, A1<5, A1=5), 0), "gt 5", "lt 5", "eq 5") 

What's going on is that 'arr' returns a boolean array, 'MATCH' finds the position of the first TRUE, and 'CHOOSE' returns the corresponding "case".

You can emulate an "else" clause by wrapping the whole thing in 'IFERROR':

=IFERROR(CHOOSE(MATCH(TRUE, arr(A1>5, A1<5), 0), "gt 5", "lt 5"), "eq 5") 

If that is too verbose for you, you can always write another VBA UDF that would bring the MATCH, CHOOSE, etc. inside, and call it like this:

=cases(arr(A1>5, A1<5, A1=5), "gt 5", "lt 5", "eq 5") 

That's not far off from your proposed syntax, and much, much simpler.

EDIT:

I see you've already come up with a (good) solution that is closer to what you really want, but I thought I'd add this anyway, since my statement above about bringing MATCH, CHOOSE, etc. inside the UDF made it look easier thatn it really is.

So, here is a 'cases' UDF:

Public Function cases(caseCondResults, ParamArray caseValues()) On Error GoTo EH Dim resOfMatch resOfMatch = Application.Match(True, caseCondResults, 0) If IsError(resOfMatch) Then cases = resOfMatch Else Call assign(cases, caseValues(LBound(caseValues) + resOfMatch - 1)) End If Exit Function EH: cases = CVErr(xlValue) End Function 

It uses a little helper routine, 'assign':

Public Sub assign(ByRef lhs, rhs) If IsObject(rhs) Then Set lhs = rhs Else lhs = rhs End If End Sub 

The 'assign' routine just makes it easier to deal with the fact that users can call UDFs with either values or range references. Since we want our 'cases' UDF to work like Excel's 'CHOOSE', we'd like to return back references when necessary.

Basically, within the new 'cases' UDF, we do the "choose" part ourselves by indexing into the param array of case values. I slapped an error handler on there so basic stuff like a mismatch between case condition results and case values will result in a return value of #VALUE!. You would probably add more checks in a real function, like making sure the condition results were booleans, etc.

I'm glad you reached an even better solution for yourself, though! This has been interesting.

MORE ABOUT 'assign':

In response to your comment, here is more about why that is part of my answer. VBA uses a different syntax for assigning an object to a variable than it does for assigning a plain value. Look at the VBA help or see this stackoverflow question and others like it: What does the keyword Set actually do in VBA?

This matters because, when you call a VBA function from an Excel formula, the parameters can be objects of type Range, in addition to numbers, strings, booleans, errors, and arrays. (See Can an Excel VBA UDF called from the worksheet ever be passed an instance of any Excel VBA object model class other than 'Range'?)

Range references are what you describe using Excel syntax like A1:Q42. When you pass one to an Excel UDF as a parameter, it shows up as a Range object. If you want to return a Range object from the UDF, you have to do it explicitly with the VBA 'Set' keyword. If you don't use 'Set', Excel will instead take the value contained within the Range and return that. Most of the time this doesn't matter, but sometimes you want the actual range, like when you've got a named formula that must evaluate to a range because it's used as the source for a validation list.