Using IfError in User Defined Function

[scecchin]

Hello

I have attached an example file. I am trying to code a user defined function, (UDF), where I am looking up, and concatenating a first name and last name, based on initials, in a table. My issue is that the table will not always have values, which is logically fine. Unfortunately when the initials are not in the table, the UDF outputs << #VALUE! >>. I am trying to replace that with no value, "", using the << IfError >> function.

I cannot seem to get it to work. The attached file has the coding for three variations of the UDF, as well as an example using a normal formula. Three scenarios are shown, one with initials and a first and last name in the table, a second scenario with initials only, in the table, and the third scenario where there are no initials and no first and last names in the table (this is what is causing me the problems).

Thanks for your help.

Sergio

 

[Somendra Misra]

Hi Sergio,

Try below code:

Function LongName_A(a As Variant)
'This function looks up the initials, "a", then returns the first name and last name, separated by a space
'However, if the intitials are not in the table, a #VALUE! is shown

    With Application
    If IsError(.VLookup(a, Range("Table1"), 2, False)) Then
        LongName_A = ""
    Else
   
        LongName_A = .VLookup(a, Range("Table1"), 2, False) & " " & .VLookup(a, Range("Table1"), 3, False)
    End If
   
    End With

End Function

Regards,

 

[scecchin]

Hello Somendra

A deceptively simple solution that works well.

Thank you for your help.

Sergio