How to better search with MATCH function in user form?

Hi. I'm using MATCH in my userform (phonebook): it goes like that: if you type person's full name (accurately!) then you get his/her full details, like his/her phone, email, etc. But what if users don't know the full name of the person they are looking for, say they only know last name, how do I use MATCH then? I guess there is a way to "soften" the search criteria?

Hi GIS2013,

See if the suggestions and / or my code from this thread from just yesterday helps.

Regards,

Robert

As your file has a xlsx extension, it's macro free. There cannot be any forms on it

Add a listbox to the userform, set it's ColumnCount to 3 and add this code to the userform module.

Private Sub TextBox1_Change()
Dim rngNames As Range
Dim arrNames
Dim arrResults
Dim lngRow As Long

    If TextBox1.Value = "" Then
        ListBox1.Clear
        Exit Sub
    End If
    
    With Sheets("Sheet1")
        Set rngNames = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With

    With rngNames
        arrNames = Evaluate(.Address & "&CHAR(45)&ROW(" & .Address & ")")
    End With

    arrNames = Application.Transpose(arrNames)

    arrResults = Filter(arrNames, TextBox1.Value)

    ListBox1.Clear

    If UBound(arrResults) = -1 Then
        ListBox1.AddItem "No matches"
    Else
        For i = LBound(arrResults) To UBound(arrResults)
            lngRow = Mid(arrResults(i), InStrRev(arrResults(i), "-") + 1)
            With ListBox1
                .AddItem
                .List(.ListCount - 1, 0) = Sheets("Sheet1").Range("A" & lngRow)
                .List(.ListCount - 1, 1) = Sheets("Sheet1").Range("B" & lngRow)
                .List(.ListCount - 1, 2) = Sheets("Sheet1").Range("C" & lngRow)
            End With
        Next i
    End If

End Sub

Hi GIS2013,

Though Norie has provided a great solution, I've attached my following code to the user form on the attached workbook as well which also uses a listbox approach.

HTH

Robert

Option Explicit

'Written by Trebor76
'Visit my website www.excelguru.net.au

'Imports all the data from A2:C[last row found from columns A to C] into the 'ListBox1' listbox and _
then splits the data into the 3 text boxes (name, phone and email) on the form
Private Sub ListBox1_Click()

    Dim intMyCounter As Integer
    
    For intMyCounter = 1 To 3

        Controls("TextBox" & intMyCounter).Value = Me.ListBox1.Column(intMyCounter - 1) 'First column is zero
        
    Next intMyCounter

End Sub
Private Sub UserForm_Initialize()

    Dim lngLastRow As Long
    
    lngLastRow = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Me.ListBox1.RowSource = Range("A2:C" & lngLastRow).Address

End Sub

Keep the listbox as it has scroll bars for large datasets. If you have the data sorted by name (you could build this in code before the form is opened) it's then no problem to scroll down and easily select (click on) the desired person.