Custom function to simplify vlookup formula

[John James]

I want to have custom functions that will simplify vlookups (and other
formulas) which have imbedded "if" formulas to eliminate errors. e.g.This
formula provided in a previous posting is ugly and somewhat annoying:
=IF(ISERROR(VLOOKUP($A2,Stock,2,0)),"",VLOOKUP($A2,Stock,2,0))

Can I replace it with a custom function that works the same but looks
something like this:
=MyVLookup($A2,Stock,2,0)

 

[Bob Phillips]

Public Function myLookup(lookup_value, _
table_array As Range, _
col_index_num As Long, _
Optional range_lookup As Boolean = True)
Dim tmp
On Error Resume Next
tmp = Application.VLookup(lookup_value, table_array, col_index_num,
range_lookup)
On Error GoTo 0
If IsError(tmp) Then
myLookup = ""
Else
myLookup = tmp
End If
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[John James]

Many thanks, Bob.

With the tmp equation all on one line it looks like it works like a charm!!!
I'll be using this on various other XL formulas to eliminate the need for
those annoying if(iserror(... portions of formulae. Beautiful!!!!

Cheers,

 

[Guest]

Hello,

I try this and work perfectly!!!. Do you know how to custom this in the same
way:

=IF(ISERROR(INDEX(A1:C3,MATCH(D2,A1:A3,),MATCH(E1,A1:C1,))),"",INDEX(A1:C3,MATCH(D2,A1:A3,),MATCH(E1,A1:C1,)))

Thanks in advance