Why doesn't isError( ) work with a vlookup sta

Question

I'm using excel 2007 and have created a UDF that includes three vlookup() statements. The function is supposed to return the sum of all three vlookup statments. In the majority of cases, only two the vlookup() statements will return a valid value the third statement will result in an NA because the lookup value is not included in the lookup range.

I have tried to trap the error and return a zero by using:

1. Application.WorksheetFunction.iferror(vlookup(...) ,0)

2. A conditional that uses If iserror(vlookup()) then ...

but I can't seem to get either approach to work. If I comment out the vlookup that I know is creating the error everything works as expected.

Does anyone know why iserror(0 and iserror() don't seem to be working or perhaps an alternative approach that will work.

Update:

Here are the three vlookup function:

product2 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productA, lookuprng, offset, False), 0)

product3 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productB, lookuprng, offset, False), 0)

product4 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productC, lookuprng, offset, False), 0)

Answer 1

Dim Res as Variant
Res = Application.WorksheetFunction.VLookup(Vndr, Range("A:a"), 1, False) 'Where Vndr is some unknown
If Res = Vndr then 
    do xyz
Else
    do 123
endif

Answer 2

You may consider to write a vlookup function with error handling lines:

Public Function v_lookup(lookup_value As String, table_array As Range, col_index_num As Integer, range_lookup As Boolean) As String

Dim result As Variant
result = Application.VLookup(lookup_value, table_array, col_index_num, range_lookup)

If IsError(result) Then result = ""
v_lookup = result

End Function

Answer 3

You can trap the error with the following:

Sub HandleVlookupErrors()
    Dim result As Variant

    result = Application.VLookup(productA, lookuprng, offset, False)
    If IsError(result) Then result = 0

End Sub

For a full explanation, please see Error Handling Within Worksheet Functions.