Vlookup --> LookupV

[Guest]

Hi,

How can I make this work?

Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_value, Error_Msg)
LookupV = Application.WorksheetFunction.VLookup(Lookup_Value,
Table_Array, Col_Index, Range_value)
If (IsError(LookupV) And Not IsEmpty(Error_Msg)) Then
LookupV = Error_Msg
End Function

It's my first attempt to make a Function.

Ola

 

[JulieD]

Hi Ola

one way:

Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_value, Error_Msg)
On Error Resume Next
LookupV = Application.WorksheetFunction.VLookup(Lookup_Value,
Table_Array, Col_Index_Num, Range_value)
If IsEmpty(LookupV) And Not IsEmpty(Error_Msg) Then
LookupV = Error_Msg
End If
End Function

Note the change from Col_Index to Col_Index_Num in the function.

Cheers
JulieD

 

[Tom Ogilvy]

Application.Vlookup will return #N/A when no value is found. This can be
tested with iserror. When you add Worksheetfunction, it raises a trappable
error (normal VBA error) which can not be handled with iserror. JulieD has
shown you one way to handle the trappable error. This shows you how to use
iserror. As JulieD noted, changed Col_Index to Col_Index_Num as well.

Function LookupV(Lookup_Value, Table_Array As Range, _
Col_Index_Num, Range_value, Error_Msg)
LookupV = Application.VLookup(Lookup_Value, _
Table_Array, Col_Index_Num, Range_value)
If IsError(LookupV) And Not IsEmpty(Error_Msg) Then _
LookupV = Error_Msg
End Function

 

[Bob Phillips]

Olla,

Always use Option Explicit at the head of your code to trap this kind of
error.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks JulieD and Tom,

It's close to perfect.

Last thing, how can I return the normal Vlookup Error messages?
=VLOOKUP(test;A1:B6;2;0) --> #NAME?
=VLOOKUP("";A1:B6;2;0) --> #N/A
And no Error_Msg has been filled in (LookupV(6;A1:B6;2;0).

Ola


Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_Value, Error_Msg)
LookupV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_Value)
If IsError(LookupV) And Not IsEmpty(Error_Msg) Then LookupV = Error_Msg
End Function

 

[JulieD]

Hi Ola

this is one option, but i'm guessing Tom or Bob (if they're still around)
will come up with something neater.

Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_value, Optional Error_Msg)
If IsMissing(Error_Msg) Then
LookupV = Application.VLookup(Lookup_Value, Table_Array,
Col_Index_Num, Range_value)
Exit Function
End If
On Error Resume Next
LookupV = Application.WorksheetFunction.VLookup(Lookup_Value,
Table_Array, Col_Index_Num, Range_value)
If IsEmpty(LookupV) Then
LookupV = Error_Msg
End If
End Function


Cheers
julieD

 

[Tom Ogilvy]

Function LookupV(Lookup_Value, Table_Array As Range, _
Col_Index_Num, Range_value, Optional Error_Msg)
LookupV = Application.VLookup(Lookup_Value, _
Table_Array, Col_Index_Num, Range_value)
If IsError(LookupV) And Not IsMissing(Error_Msg) Then _
LookupV = Error_Msg
End Function

Worked for me.

Usage =LOOKUPV(A2,D2:E11,2,FALSE)
don't put in an argument for Error_Msg unless you want it used. If you put
in "", then it will use that for the error message.

 

[Guest]

Tom and JulieD,

Thanks both for your help!
JulieD your solution worked well but Tom, yours is perfect.

Ola

And it's 15-20% faster then the "normal" formula:
=LOOKUPV(A15000;A130000;5;0;"Nothing")
=IF(ISERROR(VLOOKUP(A15;A130;5;0));VLOOKUP(A15;A130;5;0);"Nothing")