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

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
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 :

Code:
{=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 :

Code:
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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,330
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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
 
  • Like
Reactions: AOB

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
The array formula compares each surname and firstname with B1 and D1, something similar the macro should do.

Try this


Code:
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
 
  • Like
Reactions: AOB

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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/
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,186
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top