I'd drop the worksheetfunction prefix and use application.vlookup().
Function taz(a) as variant
dim res as variant
res = application.vlookup(a, Range("a1:b3"), 2, 0)
if iserror(res) then
res = application.vlookup(a, Range("a10:b12"), 2, 0)
end if
if iserror(res) then
taz = "Not found"
else
taz = res
end if
End Function
But I'd be careful with those ranges. I'd specify them in the worksheet
function:
=taz(a1,sheet2!a1:b3,sheet99!a10:b12)
so that excel will know how to calculate when a value in any of those 3 ranges
changes.
Function taz(a as range, lookuprng1 as range, lookuprng2 as range) as variant
dim res as variant
res = application.vlookup(a, lookuprng1, 2, 0)
if iserror(res) then
res = application.vlookup(a, lookuprng2, 2, 0)
end if
if iserror(res) then
taz = "Not found"
else
taz = res
end if
End Function
If you don't want to do that, then I think the least you should do is specify
the worksheets for those ranges and make the UDF volatile:
Function taz(a as range, lookuprng1 as range, lookuprng2 as range) as variant
application.volatile
dim res as variant
res = application.vlookup(a, worksheets("sheet1").range("a1:b3"), 2, 0)
if iserror(res) then
res = application.vlookup(a, worksheets("sheet99").Range("a10:b12"), 2, 0)
end if
if iserror(res) then
taz = "Not found"
else
taz = res
end if
End Function
======
And if you're not using this in a worksheet cell, then ignore the volatile
stuff. But if you are, remember that the results could be one calculation
behind--don't trust them without recalculating first.
