Index/Match with ListObject (Table)

Question

I need to know the index number for a certain name, for example when I write in TextBox1 = Sara, and click the button, Then TextBox2 should return index value opposite to that name in the table (Table1)

I tried INDEX/Match method on cell sheet, and it worked, but i tried translating it to a vba code, but I get msgbox saying

Run time 1004 Unable to get the match property of the worksheetFunction class.

my code was

Private Sub CommandButton1_Click()
Dim tbl As ListObject
Set tbl = Sheet1.ListObjects("Table1")
TextBox2.Value = Application.WorksheetFunction.Index(Sheet1.tbl.ListColumns(1), Application.WorksheetFunction.Match(TextBox1.Value, tbl.ListColumns(2), 0), 1)
End Sub

I want TextBox2 to return the index number on the table, If I write "Sam" on TextBox1, then TextBox2 should show 3.

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-07-12T08:02:23

If you use the WorksheetFunction.Match method and nothing matches it throws an exception. That is probably what you get.

So always use it like this:

Dim MatchedRowNumber As Double
On Error Resume Next 'hide the exception
MatchedRowNumber = Application.WorksheetFunction.Match(TextBox1.Value, tbl.ListColumns(2), 0)
On Error Goto 0 'always directly re-activate error reporting!!!

If MatchedRowNumber > 0 Then 'the row number will be 0 if nothing matched
    Dim LookupValue As String
    LookupValue = Application.WorksheetFunction.Index(Sheet1.tbl.ListColumns(1), MatchedRowNumber, 1)
    TextBox2.Value = LookupValue 
Else
    MsgBox "No match!"
End If

The altertative would be to use the Application.Match function instead of WorksheetFunction.Match:

Dim MatchedRowNumber As Double
MatchedRowNumber = Application.Match(TextBox1.Value, tbl.ListColumns(2), 0)

If Not IsError(MatchedRowNumber) Then 
    Dim LookupValue As String
    LookupValue = Application.Index(Sheet1.tbl.ListColumns(1), MatchedRowNumber, 1)
    TextBox2.Value = LookupValue 
Else
    MsgBox "No match!"
End If

While WorksheetFunction.Match throws an exception, Application.Match instead returns an error value that you can test with the IsError function.

Index/Match with ListObject (Table)

1 Answers