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

 

[AkaTrouble]

Bump

 

[AkaTrouble]

this is how far i have got on my own

Function NewLook(ByVal pSearch As Range, ByVal pMatrix As Range, ByVal pMatColNum As Integer) As String
    Dim s As String
    On Error Resume Next
    s = Application.WorksheetFunction.VLookup(pSearch, pMatrix, pMatColNum, False)
    If IsError(s) Then
        NewLook = ""
    Else
        NewLook = s
    End If
End Function

this uses VLookup so only enables those limitations so canot search say column 3 on a range and return column 1 value ..

but is a start

 

[Jerry Sullivan]

Hi AkaTrouble,

1. Are you wanting the function to search all columns of the lookup table/range/sheet? That might increase the chances of the function returning a value from the wrong record (instead of just having duplicates of LookUpWhat in a lookup column, you might have duplicates in other columns.

Would these arguments work better for you?
=NewLook(LookUpWhat,TableName,ColumnToSearch,OffsetColumnToReturn)

2. For this Syntax, I'm interpreting that you want the user to enter a Sheet Name for the 2nd parameter and that the lookup will use the UsedRange in that Sheet as the lookup range. Is that correct?
=NewLook(LookUpWhat,SheetNameUsedRange,OffsetColumnToReturn)

3. There's also potential for ambiguity in that the 2nd parameter could match a Sheet Name and also be a Named Range or Table. Would you want the priority to consider that 2nd parameter to be a Sheet Name if a Sheet in the active workbook matches that name?

If you'll provide those clarifications, I'll try to help.

 

[AkaTrouble]

Hi AkaTrouble,

1. Are you wanting the function to search all columns of the lookup table/range/sheet? That might increase the chances of the function returning a value from the wrong record (instead of just having duplicates of LookUpWhat in a lookup column, you might have duplicates in other columns.

Answer

i am wanting to be able to search existing data either named range, table, or used range on a sheet etc but i am trying to get functionality to search in different ways to fixed layout, for example lets say i have named range below named Contact1

NAME	ADDRESS	TELEPHONE
name1	address1	telephone1
name2	address2	telephone2
name3	address3	telephone3
name4	address4	telephone4
name5	address5	telephone5

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

i want to be able to put search for term of lets say address1 and return name or return telephone

so i would want to say lookup "address1" in named range "Contact1" column 2 and return value in named range "Contact1" column 1


Would these arguments work better for you?
=NewLook(LookUpWhat,TableName,ColumnToSearch,OffsetColumnToReturn)

Answer
i am flexible for syntax arguments as per what is needed to work

2. For this Syntax, I'm interpreting that you want the user to enter a Sheet Name for the 2nd parameter and that the lookup will use the UsedRange in that Sheet as the lookup range. Is that correct?
=NewLook(LookUpWhat,SheetNameUsedRange,OffsetColumnToReturn)

Answer
order of syntax can be what is needed i used more for description of need than requirement descriptive name would be useful or hint tip in the UDF

3. There's also potential for ambiguity in that the 2nd parameter could match a Sheet Name and also be a Named Range or Table. Would you want the priority to consider that 2nd parameter to be a Sheet Name if a Sheet in the active workbook matches that name?

Answer
agreed i had considered this i would willing to accept just knowing priority as design would suggest not duplicating names as best solution

If you'll provide those clarifications, I'll try to help.

please see answers below questions above hopefully helps

 

[Jerry Sullivan]

Here's some code you can try. I added an additional parameter for the lookup table so the syntax is:

=NewLook(LookupValue, LookupTable, ColNumToSearch, ColNumToReturn)

Function NewLook(ByVal sLookupValue As String, 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 range based on sLookupTable
 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(sLookupValue, _
   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

You might want to reconsider the error return values. I prefer to use the same error messages that Excel provides with similar functions unless there's a good reason to do otherwise.

This allows other formulas users to evaluate cells with this UDF with functions like =ISERROR() and =ISNA().

 

[AkaTrouble]

Here's some code you can try. I added an additional parameter for the lookup table so the syntax is:

=NewLook(LookupValue, LookupTable, ColNumToSearch, ColNumToReturn)


You might want to reconsider the error return values. I prefer to use the same error messages that Excel provides with similar functions unless there's a good reason to do otherwise.

This allows other formulas users to evaluate cells with this UDF with functions like =ISERROR() and =ISNA().

Fantastic brilliant !! Pretty much spot on straight off

only issue is i cant seem to get it to work with sheet name without returning "err", working fine on ranges, named ranges and tables whether on current sheet or different


have tried Sheet2 and Sheet2! (which will not allow entering)

with regard to the errors i had given thoughts on how to deal with them and thought that not returning a boolean error would help in not creating errors in subsequent use of returned data, if you think it is better to have an actual error returned rather than the strings i suggested ( i chose ##error## to highlight it was user selecting column not in range selected) and i accept that it might be better to use Excel "norms" for others to understand outputs or debugging etc. No reason for either choice maybe just a thought to return error user could understand

i really Thank You for a great piece of code and for grasping what i was after achieving

 

[Jerry Sullivan]

only issue is i cant seem to get it to work with sheet name without returning "err", working fine on ranges, named ranges and tables whether on current sheet or different

have tried Sheet2 and Sheet2! (which will not allow entering)

If using a sheet name, it should be in quotes. e.g.

=NewLook(A2, "Sheet2", 3, 2)

 

[AkaTrouble]

If using a sheet name, it should be in quotes. e.g.

=NewLook(A2, "Sheet2", 3, 2)

yepp thats my fault works great with the "" "SheetName" quotes

 

[AkaTrouble]

ok having answered the sheet question i moved onto some more testing

it sems to fail if search term is a date cell or a currency cell,

it returns a date from table if search term is text string but does not find date or currency amount if in cell example table below

Table2 on Sheet2

name	address	date	telephone
name1	address1	£1.50	telephone1
name2	address2	24/04/2017	telephone2
name3	address3	25/04/2017	telephone3
name4	address4	26/04/2017	telephone4
name5	address5	27/04/2017	telephone5
name6	address6	28/04/2017	telephone6
name7	address7	29/04/2017	telephone7
name8	address8	30/04/2017	telephone8
name9	address9	01/05/2017	telephone9

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

on sheet one

£1.50	#not found#	=NewLook(A1,Table2,3,1)
25/04/2017	#not found#	=NewLook(A2,Table2,3,1)

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>