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 withIfError
but instead need to use VBA error handling as shown in @scott craner's answer. – Tim Williams