VBA equivalent of an INDEX/MATCH array formula across a named table (XL2013)

[AOB]

Surname	FirstName	Age
Jones	Alan	20
Smith	John	35
Murphy	Paul	42
Jones	Stephen	99
Walsh	Paul	21

<tbody>
</tbody>

Hi there,

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 :

{=INDEX(tblContacts, MATCH(1,(tblContacts[Surname]=$B$1)*(tblContacts[FirstName]=$D$1),0),COLUMN(tblContacts[Age])-COLUMN(tblContacts))}

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

Any pointers?

 

[duggie33]

There are probably more ways to do this than I can imagine.

One way would be to loop through your surname column with a For Each, If Then to match your search surname AND search first name, do something if both match, check next Next.

Hope that helps,

Doug

 

[Fluff]

How about

Function GetAge(strSurname As String, strFirstName As String) As Long
   Dim i As Long, lr As Long
   Dim SRng As Range, Frng As Range, Drng As Range
   lr = Sheet14.ListObjects("tblContacts").DataBodyRange.Rows.Count
   Set SRng = Sheet14.Range("tblContacts[surname]")
   Set Frng = Sheet14.Range("tblContacts[firstname]")
   Set Drng = Sheet14.Range("tblContacts[Age]")
   For i = 1 To lr
      If SRng(i) = strSurname And Frng(i) = strFirstName Then
         GetAge = Drng(i)
         Exit For
      End If
   Next i
End Function

 

[DanteAmor]

The array formula compares each surname and firstname with B1 and D1, something similar the macro should do.

Try this

Public Function GetAge(strSurname As Range, strFirstName As Range) As Long


    Dim sh As Worksheet
    Dim tbl As ListObject
    Dim i As Double
    
    Set sh = Sheets("Static")
    Set tbl = sh.ListObjects("tblContacts")
    
    For i = 1 To tbl.ListRows.Count
        If LCase(tbl.ListColumns("Surname").DataBodyRange(i)) = LCase(strSurname) And _
           LCase(tbl.ListColumns("firstname").DataBodyRange(i)) = LCase(strFirstName) Then
            GetAge = tbl.ListColumns("Age").DataBodyRange(i)
            Exit For
        End If
    Next
End Function

 

[AOB]

Thanks everybody

I was wondering if there was some native method, or combination of methods, inherent to the ListObject that would perform multiple criteria searches that didn't require looping

(Otherwise what's the advantage of maintaining data in a ListObject over, say, a basic Range object and just iterating over that instead)

But fair enough - if there isn't, there isn't! Some nice suggestions here, I'll play around with them and see what works most efficiently

Thanks everybody

AOB

 

[DanteAmor]

Thanks everybody

I was wondering if there was some native method, or combination of methods, inherent to the ListObject that would perform multiple criteria searches that didn't require looping

(Otherwise what's the advantage of maintaining data in a ListObject over, say, a basic Range object and just iterating over that instead)

But fair enough - if there isn't, there isn't! Some nice suggestions here, I'll play around with them and see what works most efficiently

Thanks everybody

AOB

https://www.excelefficiency.com/excel-table-benefits/