Anyone good with Functions (UDF) for lookups

[AkaTrouble]

hiya peeps
as per the title i am after a UDF Function for lookups (maybe at first glance is re-inventing the wheel)

examples of how i see the function syntax in cell

=NewLook(LookUpWhat,NamedRange,OffsetColumnToReturn)
=NewLook(LookUpWhat,TableName,OffsetColumnToReturn)
=NewLook(LookUpWhat,SheetNameUsedRange,OffsetColumnToReturn)
=NewLook(LookUpWhat,EnteredRange,OffsetColumnToReturn)

LookUpWhat can equal cell reference, text string, number, date

if the lookup range does not have OffsetColumnToReturn the return text string "##err##"

if LookUpWhat does not exist in Search range return text string "#not found#"

if has no entry return "" null string not error, and if has error in source but OffsetColumnToReturn exists return "" null string

it is envisaged that the lookup range will not have duplicates (unless you have any ideas how to deal with such)

the lookup ranges may be on hidden sheets

it is also possible that the needed return value will be in a preceding column to the LookUpWhat (which highlights a possible need for 4th syntax needed which column of search range to LookUpWhat)

i understand there is many variables here and it may not be possible to have one UDF to cover all, but thought best to start explaining what i would like at beginning rather than adding requirements as solutions offered. If each type of search range needs own UDF then i can understand that limitation.

hopefully i have made some sense of a difficult conundrum without trying to answer my own question and thanks for reading

 

[Jerry Sullivan]

Please try this version. Its main differences are:

1. Using a Variant instead of a String for the data type of the LookupValue
2. Using the .Value2 property of the LookupValue if it is a Range object.

Function NewLook(ByVal vLookupValue As Variant, ByVal vLookupTable As Variant, _
   ByVal lColNumToSearch As Long, ByVal lColNumToReturn As Long) As Variant

'--Custom Lookup Function that uses Index-Match and allow input
'  lookup range to be entered as string or direct range reference
'--If string is name of worksheet in this workbook, then the usedrange
'  on that sheet will be used as lookup range.
'--Returns custom error messages that can be modified in the code.

 Dim rLookupRange As Range
 Dim sReturn As String
 Dim vMatch As Variant, vValue As Variant
 Dim wkb As Workbook
 Dim wksFormula As Worksheet
 
 '--optional, will keep values updated but recalculate more often
 Application.Volatile

 Set wkb = ThisWorkbook
 
 '--interpret lookup value based on vLookupValue data type
 If TypeName(vLookupValue) = "Range" Then
      vLookupValue = vLookupValue.Cells(1).Value2
 End If
  
 '--interpret lookup range based on vLookupTable data type
 Select Case TypeName(vLookupTable)
   '--exceptions are handled by test for range not found
   Case "String"
      If bWorkSheetExists(wkb:=wkb, sSheetName:=vLookupTable) Then
         Set rLookupRange = wkb.Sheets(vLookupTable).UsedRange
      Else
         '--get context to evaluate expression
         If TypeName(Application.Caller) = "Range" Then
            Set wksFormula = Application.Caller.Worksheet
            If TypeName(wksFormula.Evaluate(vLookupTable)) = "Range" Then
               Set rLookupRange = Evaluate(vLookupTable)
            End If
         End If
      End If
      
   Case "Range"
     Set rLookupRange = vLookupTable
   Case Else
 End Select
 
 '--test for range not found
 If rLookupRange Is Nothing Then
   sReturn = "err"
   GoTo ExitProc
 End If
 
 '--test for invalid column number reference
 If rLookupRange.Columns.Count < lColNumToSearch Or _
   rLookupRange.Columns.Count < lColNumToReturn Or _
   lColNumToSearch < 1 Or _
   lColNumToReturn < 1 Then
   sReturn = "##err##"
   GoTo ExitProc
 End If
 
 '--find match
 vMatch = Application.Match(vLookupValue, _
   Application.Index(rLookupRange, 0, lColNumToSearch), 0)
 If IsError(vMatch) Then
   '--match not found
   sReturn = "#not found#"
 Else
   vValue = Application.Index(rLookupRange, CLng(vMatch), lColNumToReturn)
   sReturn = IIf(IsError(vValue), vbNullString, vValue)
 End If

ExitProc:
 NewLook = sReturn
End Function

Function bWorkSheetExists(ByVal wkb As Workbook, _
   ByVal sSheetName As String) As Boolean
 '--returns True if worksheet with specified name exists
 '  in specified workbook. Else returns False
 
 On Error Resume Next
 bWorkSheetExists = _
   LCase(wkb.Worksheets(sSheetName).Name) = LCase(sSheetName)
End Function

 

[AkaTrouble]

Please try this version. Its main differences are:

yepp that seems to have fixed the issue, Thanks Very much for follow up fix,

have tested a beta file on the friend i am doing the project for and he is over the moon as a total PC phobe and scared to click the mouse he created his own custom report from his own data in 10 (ish) minutes

and he passes on his Thanks to tou too.