This appears to be returning the same results as the formula (more or less). I'm not falling through to the lookup for Connie, et al, so either I didn't figure out what to put in for test data or the logic is such that it never gets there.

HTH

Function AltEngName(varName As Variant, varShip As Variant) As Variant
On Error Resume Next
    If varName = varShip Then
    AltEngName = varName
    ElseIf varName = vbNullString Then
    AltEngName = varShip
    ElseIf Len(Replace(Trim(varName), " ", "  ")) = Len(Trim(varName)) Then
    AltEngName = Application.WorksheetFunction.Lookup([D2], [Sheet2!$D$2:$D$110], [Sheet2!$A$2:$A$110])
    ElseIf Not IsError(Application.WorksheetFunction.Match(varName, _
Array("CONNIE", "DARLENE", "MICHAEL", "MARIANO", "JAMES"))) Then
    AltEngName = varShip
    Else
    AltEngName = varName
    End If
End Function