Why reinvent the wheel?

Worksheet functions will do this and will be more efficient than a UDF.

=IF(COUNTIF(Sheet2!A$1:A$366,E4),VLOOKUP(E4,Sheet2 !A$1:B$366,2,0),"")

--
Biff
Microsoft Excel MVP


"Danny" wrote in message
...
I am trying to do a custom lookup function where the range remains constant
a1:b365 and I am trying to lookup a long list where some references are
blank
and do not want #NA throughout the document so I tried:

Function Lookupdate(datehere)
'
If datehere = 0 Then
Lookupdate = ""
Else
If datehere < 0 Then {is < not equal to?}
Lookupdate = Application.Lookup(datehere, Sheet2!Range(A1:b366))

End If
End If
End Function

where datehere is a date on a calendar and I am trying to lookup that date
in column a on sheet 2 and return column b (importance of date - holiday,
birthday, appointment,etc)from sheet 2.
lookup(e4,sheet2!a1:b366) works in excel but when trying to do this in a
custom function with vba I cannot get it to work.
I need someway to do this for a long list with out getting #N/A and also
when I try to autofill the lookup function in excel it changes the range
also.