I literally dreamed about this last night. It would be wonderful if Excel supported formulas like this:
=LET(value=MATCH(item,range,0), IF(ISERROR(value), 0, value))
If you're into Lisp-y languages, it'd look like this:
(let ((value (match item range 0))) (if (is-error? value) 0 value))
The function call
=LET(name=binding, expression) would create a local range name named name, bound (equal) to the value returned by binding, to be used during the evaluation of expression. In the example above, during the evaluation of
IF(ISERROR(value), 0, value))<,
value would be bound to the value returned by
MATCH(item, range, 0).
It's worth pointing out that this is slightly different from how normal Excel range names work. Range names in Excel work through textual substitution. With textual substitution, the initial expression would be logically equivalent to this:
=IF(ISERROR(MATCH(item, range, 0)), 0, MATCH(item, range, 0)))
In other words, Excel would treat every instance of value as if
MATCH(item, range, 0) was explictly spelled out. This means there are two calls to MATCH and two potential searches through the range. While it's possible that Excel optimizes the second search away, I'm not sure that anybody outside of Microsoft can know for sure how this is handled.
Microsoft's current reccomendation for handling the specific
ISERROR scenario in the first expression is this VBA function:
Function IfError(formula As Variant, show As String) On Error GoTo ErrorHandler If IsError(formula) Then IfError = show Else IfError = formula End If Exit Function ErrorHandler: Resume Next End Function
This isn't bad, but it requires that spreadsheet authors and readers understand VBA. It also imposes significant performance costs: calling into VBA from a worksheet takes time.