Vlookup -- LookupV
 

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

Vlookup -- LookupV
 

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



"Ola" wrote in message
...
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

Vlookup -- LookupV
 

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

--
Regards,
Tom Ogilvy


"Ola" wrote in message
...
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

Vlookup -- LookupV
 

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)


"JulieD" wrote in message
...
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



"Ola" wrote in message
...
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

Vlookup -- LookupV
 

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

Vlookup -- LookupV
 

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

"Ola" wrote in message
...
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

Vlookup -- LookupV
 

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.

--
Regards,
Tom Ogilvy

"Ola" wrote in message
...
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

LOOKUPV
 

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;A1:P30000;5;0;"Nothing")
=IF(ISERROR(VLOOKUP(A15;A1:P30;5;0));VLOOKUP(A15;A 1:P30;5;0);"Nothing")