Why does IfError return error?

Question

I'm trying to create a wrapper for the INDEX/MATCH lookup method (which is faster/better than the regular VLOOKUP method) by creating a custom function and loading it afterwards as an add-in.

So far so good, but my desired behavior is that, when the value to be looked up is not found, it should return blank ("") and not #VALUE!. So I am trying to use the IfError WorksheetFunction in order to achieve this the same way I would on an Excel sheet:

Function FastLookup(parLookupKey As Variant, parLookupRange As Range, parReturnRange As Range) As Variant

    FastLookup = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Index(parReturnRange, Application.WorksheetFunction.Match(parLookupKey, parLookupRange, 0)), "")

End Function

Again, this still returns #VALUE! if a value is not found on the parLookupRange range.

Needless to say, if I use =IFERROR(FastLookup(H6,E3:E6,F3:F6),"") directly on the Excel sheet, it works.

Any ideas on how to make the VBA version of IfError work?

Application.WorksheetFunction.Match causes a VBA run-time error if there's no match found - you cannot trap that type of error with IfError but instead need to use VBA error handling as shown in @scott craner's answer. — Tim Williams

Scott Craner Scott Craner · Accepted Answer · 2017-02-14T20:47:37

Because the error never makes it past the MATCH function in vba. As soon as that throws an error the code will stop.

Use this instead:

Function FastLookup(parLookupKey As Variant, parLookupRange As Range, parReturnRange As Range) As Variant

    Dim t As Long
    On Error Resume Next
    t = Application.WorksheetFunction.Match(parLookupKey, parLookupRange, 0)
    On Error GoTo 0
    If t > 0 Then
        FastLookup = parReturnRange(t)
    Else
        FastLookup = ""
    End If

End Function

We now capture the error and ignore it. Then we test for it and return the correct value.

Why does IfError return error?

1 Answers