Create VBA Custom Formula to replicate vlookup

J

jcueland

New Member
Joined
Mar 19, 2007
Messages
42
Hello,

I'm very new to VBA and so I think I have a question that should be easy to answer. What I'm trying to do is create a custom function that performs just like vlookup, except if the vlookup returns an error I would like it to show zero. Effectively, the function would mimic this formula: IF(ISERROR(VLOOKUP(...)),0,(VLOOKUP(...)).

I want to create this custom function b/c I find myself typing the above function I'd say once a week (for about a year now). So, I'm trying to create my own custom function.

Below I've tried to create my custom vlookup function, but it still acts just like the normal vlookup - if there is an error it displays an error instead of zero.

Again, I'm new to VBA so my code below is probably full of novice errors. Any help would be greatly appreciated,
Justin


Public Function ErrorVlookup(zlookupvalue, zrange, zColumnIndex, zLogic) As Single
Dim zVar As String

zVar = WorksheetFunction.vlookup(zlookupvalue, zrange, zColumnIndex, zLogic)
On Error GoTo ErrorHandler

ErrorVlookup = zVar
Exit Function

ErrorHandler:
ErrorVlookup = 0

End Function
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Here's a UDF I had created for that. But honestly if you use it that much, you may end up with performance issues. Custom UDF Functions are slower than built in functions. Especially if used Alot. Like over 1000 or so...

but anyway

Code: 
Function myvlookup(myval As Variant, myrange As Range, cols As Integer)
If WorksheetFunction.CountIf(myrange, myval) = 0 Then GoTo notfound
If WorksheetFunction.VLookup(myval, myrange, cols, 0) = "" Then GoTo blank
myvlookup = WorksheetFunction.VLookup(myval, myrange, cols, 0)
GoTo found
blank:
myvlookup = "The resulting cell was balnk"
GoTo found
notfound:
myvlookup = "The Value was not found"
found:
End Function

Replace "The resulting cell was balnk" with whatever you want it to say when the resulting cell is blank (vlookup returns 0)

Replace "The Value was not found" with whatever you want it to say when the lookup value was not found (vlookup returns #N/A)
 
Upvote 0
Thanks for the function. I tried it out and it doesn't do what I was expecting (I think). I tried using the function to look up a value that wasn't in the range (so vlookup would normally return #Value!).

Using your myvlookup it appears that it also returns #Value! if the lookup value is not in the range. In this situation I would want to display a zero.

Do you have any advice on how to customize your function so it will always display a zero no mater the error?

Thanks a lot for you help! I really appreciate it.
 
Upvote 0
it works exactly as expected for me, I get "The Value was not found" if the lookup value is not in the range.

you can just change the 2 custom error messages at the bottom to
"0"

instead of

"The resulting cell was balnk"
"The Value was not found"
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Macro Replicating AutoSum Functionality
Replies
3
Views
93
Joe4
Joe4
H
  • Solved
Using Macro/VLOOKUP to return format of cell
Replies
9
Views
124
mumps
mumps
F
  • Question
Formula to insert a picture based on various cell values, w/out VBA, Macros, etc.
Replies
4
Views
120
Domenic
D
H
  • Question
VLOOKUP Issue with Approximate Match - Need Help Categorizing Customers
Replies
9
Views
522
Peter_SSs
Peter_SSs
T
  • Question
VLOOKUP + INDIRECT overlap (maybe not possible)
Replies
5
Views
221
TXNCPO
T

Forum statistics

Threads
1,215,064
Messages
6,122,942
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top