Rewrite a table lookup function in VBA

Found the GetTableEntry function below on this site:

It works but I would like to rewrite it so I could use an actual named Excel table as the LookupTable parameter instead of a range.

It workes with this formula: =gettableentry(A1:C3,"Ben","Orange")
I would like to rewrite to work with this: =gettableentry("tblFruits","Ben","Orange")

Public Function GetTableEntry( _
            ByVal LookupTable As Range, _
            ByVal RowHeader As String, _
            ByVal ColHeader As String)

    With Application
        .Volatile
        
        With .WorksheetFunction
            GetTableEntry = LookupTable.Parent.Cells( _
                .Match(RowHeader, LookupTable.Columns(1), 0) + LookupTable.row - 1, _
                .Match(ColHeader, LookupTable.Rows(1), 0) + LookupTable.Column - 1).Value
        End With
    End With
    
End Function

Open in new window

hallpettAsked:
Who is Participating?
 
MacroShadowCommented:
No need to rewrite anything, just pass the table name.

i.e. GetTableEntry(tblFruits, "Ben", "Orange")
0
 
NBVCCommented:
Just a quick observation... tblFruits should not be in quotes if it is a named range.
0
 
hallpettAuthor Commented:
NBVC, your observation gave me an idea. It workes when I change:
=gettableentry("tblFruits","Ben","Orange")
to:
=gettableentry(tblFruits[#Alle],"Ben","Orange")

( the [#Alle] is Norwegian Excel, guess it should be [#All] in English Version)

Thanks!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
NBVCCommented:
You're welcome
0
 
NBVCCommented:
how is mine assisted, when it is the same solution as the other, and given first?
0
 
hallpettAuthor Commented:
Sorry. I just share the points between you since you gave med the idea to do some more testing and MacroShadow presicely told me that there was no need for rewriting. Probably should have given you the "Accepted solution", but was not aware that this ment something.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.