- Dec 15, 2010
- Office Version
Say I have a table that looks something like the above (sorry, the editor won't let me insert it in the correct position in the post)
Let's call it tblContacts
Within a sheet, I can perform a multiple criteria lookup in that table using an INDEX/MATCH array formula like so :
Nice and dynamic - even if the table expands, contracts or moves, as long as the column names remain the same, the formula will always work
What is the (best) equivalent method via a VBA function to do a multiple criteria lookup in a named table?
Á la :
Public Function GetAge(strSurname As String, strFirstName As String) As Long Dim shtData As Worksheet Dim lsoContacts As ListObject Set shtData = ThisWorkbook.Sheets("Static") Set lsoContacts = shtData.ListObjects("tblContacts") .... End Function
Given the data is formally structured in a table, it seems this should be something very straightforward to do in VBA (like it is via the formula) but it's not obvious to me?
I mean doing it via the ListObject object directly, not via Application.WorksheetFunction or Worksheet.Evaluate