VBA Excel Xlookup - How to use a Multi string lookup to find a record

[Debs_au]

I have a spreadsheet containing columns of data from A to V, where each row is a Member record.

Is it possible to use nested Xlookup in VBA to find a record based on 3 string fields, for example:

Given_Name (col F), Surname (col G), and Street address (col J)?  And if you can, how do you manage to pull the whole record from the sheet into a Variant array "GetMemberData" ?

I have tried to following code, thinking that the returned record (A:V) from the first lookup would be overwritten by the second and third; but it hasn't worked, and I'm more confused than when I started. 

Would appreciate some help here. Kind thanks in advance.

' Previous "If-then-else" tests and xlookups work for the individual unique values (Member-number or Phone_number) work fine, but not this sectioin which are also entered into text boxes on the same user form, where the Member number or phone number are unknown.
'
ElseIf I_Given <> "" And I_Surname <> "" And I_Street <> "" Then
       GetMemberData = (WorksheetFunction.XLookup(I_Given, Range("F:F"), Range("A:V")).Value) & (WorksheetFunction.XLookup(I_Surname, Range("G:G"), Range("A:V")).Value) & (WorksheetFunction.XLookup(I_Street, Range("J:J"), Range("A:V")).Value)
End If

( Note: This question is in relation to the same Membership spreadsheet and "UpdateMemberForm" as in a previous subject "Run-time error 1004 : Unable to get the XLookup property of the Worksheet function class")

[#2 · Accepted solution]

@Debs_au Rather than using a function I would use the Autofilter object:

Private Sub Btn_Find_Member_Click()
    SetSwitch = True
    Dim GetMemberData As Variant
    Do Until SetSwitch = False
        If Len(I_Mem_Num.Value) > 0 Then
            'existing code
        ElseIf Len(I_Phone.Value) > 0 Then
            'existing code
        ElseIf IsEmpty(I_Given) = False And IsEmpty(I_Surname) = False And IsEmpty(I_Street) = False Then
            'Clear filter
            Sheet1.Range("A4:V307").AutoFilter
            'Now filter on entries
            Sheet1.Range("A4:V307").AutoFilter 6, I_Given
            Sheet1.Range("A4:V307").AutoFilter 7, I_Surname
            Sheet1.Range("A4:V307").AutoFilter 9, I_Street
            With Sheet1.Range("A4:V307").Offset(1).SpecialCells(xlCellTypeVisible)
                If .Rows.Count = 1 Then
                    GetMemberData = .Value
                ElseIf .Rows.Count > 1 Then
                    MsgBox "Found more than one match!"
                    Exit Sub
                Else
                    MsgBox "No match found!"
                    Exit Sub
                End If
            End With
            SetSwitch = False
            Exit Do
        Else
            SetSwitch = True
            MsgBox "There is not enough unique information to search. Please use [Member number] OR [Phone number] OR [Given-Name and Surname and Street]"
        End If
    Loop
    ' >>>>>  Make Text Boxes Visible <<<<<
    I_Suburb.Visible = True
    I_Postcode.Visible = True
    I_Birth.Visible = True
    I_Age.Visible = True
    I_Comment.Visible = True
    I_Email.Visible = True
    I_Mem_Status.Visible = True
    I_Mem_Receipt.Visible = True
    I_Mem_Date_Paid.Visible = True
    I_Joining_Date.Visible = True
    I_Film_Fee.Visible = True
    I_Film_Receipt.Visible = True
    I_Film_Date_Paid.Visible = True
    Mem_Category.Visible = True
    Btn_Gender_Male.Visible = True
    Btn_Gender_Female.Visible = True
    Btn_Gender_Other.Visible = True
    Btn_Vote_Yes.Visible = True
    Btn_Vote_No.Visible = True
    Btn_Photo_Yes.Visible = True
    Btn_Photo_No.Visible = True
    '
    ' >>>>> Put Retrieved Data into Text Boxes <<<<<<
    I_Mem_Num.Value = GetMemberData(1, 1)
    I_Mem_Status.Value = GetMemberData(1, 2)
    I_Mem_Receipt.Value = GetMemberData(1, 3)
    I_Mem_Date_Paid.Value = GetMemberData(1, 4)
    Mem_Category.Value = GetMemberData(1, 5)
    I_Given.Value = GetMemberData(1, 6)
    I_Surname.Value = GetMemberData(1, 7)
    I_Phone.Value = GetMemberData(1, 8)
    I_Email.Value = GetMemberData(1, 9)
    I_Street.Value = GetMemberData(1, 10)
    I_Suburb.Value = GetMemberData(1, 11)
    I_Postcode.Value = GetMemberData(1, 12)
    If GetMemberData(1, 13) = "Male" Then
        Btn_Gender_Male.Value = True
    ElseIf GetMemberData(1, 13) = "Female" Then
        Btn_Gender_Female.Value = True
    Else: Btn_Gender_Other.Value = True
    End If
    I_Birth.Value = GetMemberData(1, 14)
    I_Age.Value = GetMemberData(1, 15)
    I_Joining_Date.Value = GetMemberData(1, 16)
    If GetMemberData(1, 17) = "Yes" Then
        Btn_Vote_Yes.Value = True
    Else: Btn_Vote_No.Value = True
    End If
    If GetMemberData(1, 18) = "Yes" Then
        Btn_Photo_Yes.Value = True
    Else: Btn_Photo_No.Value = True
    End If
    ' Column S (19) >Special Needs = not used
    I_Comment.Value = GetMemberData(1, 20)
    I_Film_Fee.Value = GetMemberData(1, 21)
    I_Film_Receipt.Value = GetMemberData(1, 22)
    '    I_Film_Date_Paid.Value = GetMemberData(1, 23)
End Sub

[#3]

@Jan Karel Pieterse 

Hi Jan, thank you for your suggested code here. Unfortunately, it's not a solution that will suit the end application in this instance as end-users of the forms wont have access to the spreadsheet itself. But I'm sure it will be useful for many others; and as I have only been programming for a few weeks, I have enjoyed the learning experience.

[#4]

@Debs_au 

Hi All, I have solved my problem with XLookup looking up more than 1 string (ie look for both a given name and a surname, and return the record.

If the given name is in cell A1, and the surname is in cell B1.

The column to lookup to find the given name is Column F

The column to lookup to find the surname name is Column G

And then return the whole record on the row (columns A to V) if there is a match

With an error message "Record Not Found" if a match cant be found.

=XLOOKUP(A1&B1,F:F&G:G,A:V,"Record Not Found")

I hope this helps others with a similar problem.