Search code examples
excelindexingmatchworksheet-function

Worksheet Function with Index/Match

I believe my syntax is wrong, would someone mind pointing out the issue?

Thanks in advance

 result = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Index_
(Range("Sheet10!$AC$40:$AC$118"), Application.WorksheetFunction.Match(Range("Sheet10!E3"),_
 Range("Sheet10!$AD$40:$AD$118"), 0)), "")
Solution

  • The IFERROR function cannot be used as a WorksheetFunction object. The formula will work as long as there is no error but will choke when WorksheetFunction.IfError comes into play to return the default value (e.g. zero-length string).

    Sub ject()
    Dim result As Variant
    'this works if a match is found
    result = Application.WorksheetFunction.IfError( _
                Application.WorksheetFunction.Index(Range("Sheet10!AC40:AC118"), _
                Application.WorksheetFunction.Match(Range("Sheet10!E3"), Range("Sheet10!AD40:AD118"), 0)), "")
    Debug.Print result
End Sub

    You might try the Application.Evaluate method instead.

    Sub jective()
    Dim result As Variant
    'this works on match or no match
    result = Application.Evaluate("IFERROR(INDEX(Sheet10!AC40:AC118, " & _
                "MATCH(Sheet10!E3, Sheet10!AD40:AD118, 0)), ""nothing"")")
    Debug.Print result
End Sub

    Typically, there can be Application.Index(... or WorksheetFunction.Index(... but there is no need for Application.WorksheetFunction.Index(....

    When referring to a static cell address with a string, there is usually no need for the $ absolute makers since a string is not going to change. One possible exception would be when you are using the string to fill a large number of cells with a formula; not getting the result from evaluating a single formula.