Two-column match for row number

Question

I've been using a brute force line of code doing a two-column match, looking something like this:

For K = 1 To TPrice.ListRows.Count
    If TPrice.DataBodyRange(K, 2).Value = str3 And TPrice.DataBodyRange(K, 3).Value = project_name Then
        r = K + 2
        Exit For
    End If
Next K

Where the variable str3 is non-unique on column 2, and the variable project_name is also non-unique on column 3. But are 'matched' when they are on the same row. The data set could be something like:

where I would like to match john smith for instance, and the return value would be table row 3, or row number 4. But obviously, the data set is going to be a lot bigger and I'll have to ply some data off the other columns in the same row, for instance, john smith's employment history etc.

I've been most recently exposed to some array knowledge (Thank you Ralph!) and contemplating one like this:

Public Sub Finding_John_Smith()
Dim vArr As Variant
Dim lCount As Long
Dim V As Long

vArr = ThisWorkbook.Worksheets("names").ListObjects(1).DataBodyRange.Value2

    For lCount = LBound(vArr) To UBound(vArr)
        Select Case vArr(lCount, 1)
        Case "john"
            If vArr(lCount, 2) = "Smith" Then
                MsgBox "you found a match!"
                V = lCount
            End If
        Case Else
            MsgBox "name cannot be found!"
        End Select
    Next lCount

    End Sub

I was just wondering among you experts out there which one might be faster, or any personal recommendations you might have! Thank you so much for your time.

Answer 1

You could adapt the following to get something that works...

Dim FullName As String: FullName = "John Smith"

Dim tbl As ListObject: Set tbl = ActiveSheet.ListObjects("Table1")
Dim SearchArr() As String: SearchArr = Split(FullName, " ")

Application.Goto tbl
With tbl
    .Range.AutoFilter Field:=1, Criteria1:=SearchArr(0)
    .Range.AutoFilter Field:=2, Criteria1:=SearchArr(1)
    Dim Rng As Range: Set Rng = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
    .Range.AutoFilter Field:=1
    .Range.AutoFilter Field:=2
End With