SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1609655%22%20slang%3D%22en-US%22%3EVBA%20Excel%20Xlookup%20-%20How%20to%20use%20a%20Multi%20string%20lookup%20to%20find%20a%20record%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609655%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20containing%20columns%20of%20data%20from%20A%20to%20V%2C%20where%20each%20row%20is%20a%20Member%20record.%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20use%20nested%20Xlookup%20in%20VBA%20to%20find%20a%20record%20based%20on%203%20string%20fields%2C%20for%20example%3A%3C%2FP%3E%3CP%3EGiven_Name%20(col%20F)%2C%20Surname%20(col%20G)%2C%20and%20Street%20address%20(col%20J)%3F%26nbsp%3B%20And%20if%20you%20can%2C%20how%20do%20you%20manage%20to%20pull%20the%20whole%20record%20from%20the%20sheet%20into%20a%20Variant%20array%20%22GetMemberData%22%20%3F%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20following%20code%2C%20thinking%20that%20the%20returned%20record%20(A%3AV)%20from%20the%20first%20lookup%20would%20be%20overwritten%20by%20the%20second%20and%20third%3B%20but%20it%20hasn't%20worked%2C%20and%20I'm%20more%20confused%20than%20when%20I%20started.%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20some%20help%20here.%20Kind%20thanks%20in%20advance.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E'%20Previous%20%22If-then-else%22%20tests%20and%20xlookups%20work%20for%20the%20individual%20unique%20values%20(Member-number%20or%20Phone_number)%20work%20fine%2C%20but%20not%20this%20sectioin%20which%20are%20also%20entered%20into%20text%20boxes%20on%20the%20same%20user%20form%2C%20where%20the%20Member%20number%20or%20phone%20number%20are%20unknown.%0A'%0AElseIf%20I_Given%20%26lt%3B%26gt%3B%20%22%22%20And%20I_Surname%20%26lt%3B%26gt%3B%20%22%22%20And%20I_Street%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20GetMemberData%20%3D%20(WorksheetFunction.XLookup(I_Given%2C%20Range(%22F%3AF%22)%2C%20Range(%22A%3AV%22)).Value)%20%26amp%3B%20(WorksheetFunction.XLookup(I_Surname%2C%20Range(%22G%3AG%22)%2C%20Range(%22A%3AV%22)).Value)%20%26amp%3B%20(WorksheetFunction.XLookup(I_Street%2C%20Range(%22J%3AJ%22)%2C%20Range(%22A%3AV%22)).Value)%0AEnd%20If%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E(%20Note%3A%20This%20question%20is%20in%20relation%20to%20the%20same%20Membership%20spreadsheet%20and%20%22UpdateMemberForm%22%20as%20in%20a%20previous%20subject%20%22Run-time%20error%201004%20%3A%20Unable%20to%20get%20the%20XLookup%20property%20of%20the%20Worksheet%20function%20class%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610192%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Excel%20Xlookup%20-%20How%20to%20use%20a%20Multi%20string%20lookup%20to%20find%20a%20record%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610192%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768560%22%20target%3D%22_blank%22%3E%40Debs_au%3C%2FA%3E%26nbsp%3BRather%20than%20using%20a%20function%20I%20would%20use%20the%20Autofilter%20object%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Btn_Find_Member_Click()%0A%20%20%20%20SetSwitch%20%3D%20True%0A%20%20%20%20Dim%20GetMemberData%20As%20Variant%0A%20%20%20%20Do%20Until%20SetSwitch%20%3D%20False%0A%20%20%20%20%20%20%20%20If%20Len(I_Mem_Num.Value)%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20'existing%20code%0A%20%20%20%20%20%20%20%20ElseIf%20Len(I_Phone.Value)%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20'existing%20code%0A%20%20%20%20%20%20%20%20ElseIf%20IsEmpty(I_Given)%20%3D%20False%20And%20IsEmpty(I_Surname)%20%3D%20False%20And%20IsEmpty(I_Street)%20%3D%20False%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20'Clear%20filter%0A%20%20%20%20%20%20%20%20%20%20%20%20Sheet1.Range(%22A4%3AV307%22).AutoFilter%0A%20%20%20%20%20%20%20%20%20%20%20%20'Now%20filter%20on%20entries%0A%20%20%20%20%20%20%20%20%20%20%20%20Sheet1.Range(%22A4%3AV307%22).AutoFilter%206%2C%20I_Given%0A%20%20%20%20%20%20%20%20%20%20%20%20Sheet1.Range(%22A4%3AV307%22).AutoFilter%207%2C%20I_Surname%0A%20%20%20%20%20%20%20%20%20%20%20%20Sheet1.Range(%22A4%3AV307%22).AutoFilter%209%2C%20I_Street%0A%20%20%20%20%20%20%20%20%20%20%20%20With%20Sheet1.Range(%22A4%3AV307%22).Offset(1).SpecialCells(xlCellTypeVisible)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20.Rows.Count%20%3D%201%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GetMemberData%20%3D%20.Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ElseIf%20.Rows.Count%20%26gt%3B%201%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20%22Found%20more%20than%20one%20match!%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20%22No%20match%20found!%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20%20%20%20%20%20%20%20%20SetSwitch%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Do%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20SetSwitch%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20%22There%20is%20not%20enough%20unique%20information%20to%20search.%20Please%20use%20%5BMember%20number%5D%20OR%20%5BPhone%20number%5D%20OR%20%5BGiven-Name%20and%20Surname%20and%20Street%5D%22%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Loop%0A%20%20%20%20'%20%26gt%3B%26gt%3B%26gt%3B%26gt%3B%26gt%3B%20%20Make%20Text%20Boxes%20Visible%20%26lt%3B%26lt%3B%26lt%3B%26lt%3B%26lt%3B%0A%20%20%20%20I_Suburb.Visible%20%3D%20True%0A%20%20%20%20I_Postcode.Visible%20%3D%20True%0A%20%20%20%20I_Birth.Visible%20%3D%20True%0A%20%20%20%20I_Age.Visible%20%3D%20True%0A%20%20%20%20I_Comment.Visible%20%3D%20True%0A%20%20%20%20I_Email.Visible%20%3D%20True%0A%20%20%20%20I_Mem_Status.Visible%20%3D%20True%0A%20%20%20%20I_Mem_Receipt.Visible%20%3D%20True%0A%20%20%20%20I_Mem_Date_Paid.Visible%20%3D%20True%0A%20%20%20%20I_Joining_Date.Visible%20%3D%20True%0A%20%20%20%20I_Film_Fee.Visible%20%3D%20True%0A%20%20%20%20I_Film_Receipt.Visible%20%3D%20True%0A%20%20%20%20I_Film_Date_Paid.Visible%20%3D%20True%0A%20%20%20%20Mem_Category.Visible%20%3D%20True%0A%20%20%20%20Btn_Gender_Male.Visible%20%3D%20True%0A%20%20%20%20Btn_Gender_Female.Visible%20%3D%20True%0A%20%20%20%20Btn_Gender_Other.Visible%20%3D%20True%0A%20%20%20%20Btn_Vote_Yes.Visible%20%3D%20True%0A%20%20%20%20Btn_Vote_No.Visible%20%3D%20True%0A%20%20%20%20Btn_Photo_Yes.Visible%20%3D%20True%0A%20%20%20%20Btn_Photo_No.Visible%20%3D%20True%0A%20%20%20%20'%0A%20%20%20%20'%20%26gt%3B%26gt%3B%26gt%3B%26gt%3B%26gt%3B%20Put%20Retrieved%20Data%20into%20Text%20Boxes%20%26lt%3B%26lt%3B%26lt%3B%26lt%3B%26lt%3B%26lt%3B%0A%20%20%20%20I_Mem_Num.Value%20%3D%20GetMemberData(1%2C%201)%0A%20%20%20%20I_Mem_Status.Value%20%3D%20GetMemberData(1%2C%202)%0A%20%20%20%20I_Mem_Receipt.Value%20%3D%20GetMemberData(1%2C%203)%0A%20%20%20%20I_Mem_Date_Paid.Value%20%3D%20GetMemberData(1%2C%204)%0A%20%20%20%20Mem_Category.Value%20%3D%20GetMemberData(1%2C%205)%0A%20%20%20%20I_Given.Value%20%3D%20GetMemberData(1%2C%206)%0A%20%20%20%20I_Surname.Value%20%3D%20GetMemberData(1%2C%207)%0A%20%20%20%20I_Phone.Value%20%3D%20GetMemberData(1%2C%208)%0A%20%20%20%20I_Email.Value%20%3D%20GetMemberData(1%2C%209)%0A%20%20%20%20I_Street.Value%20%3D%20GetMemberData(1%2C%2010)%0A%20%20%20%20I_Suburb.Value%20%3D%20GetMemberData(1%2C%2011)%0A%20%20%20%20I_Postcode.Value%20%3D%20GetMemberData(1%2C%2012)%0A%20%20%20%20If%20GetMemberData(1%2C%2013)%20%3D%20%22Male%22%20Then%0A%20%20%20%20%20%20%20%20Btn_Gender_Male.Value%20%3D%20True%0A%20%20%20%20ElseIf%20GetMemberData(1%2C%2013)%20%3D%20%22Female%22%20Then%0A%20%20%20%20%20%20%20%20Btn_Gender_Female.Value%20%3D%20True%0A%20%20%20%20Else%3A%20Btn_Gender_Other.Value%20%3D%20True%0A%20%20%20%20End%20If%0A%20%20%20%20I_Birth.Value%20%3D%20GetMemberData(1%2C%2014)%0A%20%20%20%20I_Age.Value%20%3D%20GetMemberData(1%2C%2015)%0A%20%20%20%20I_Joining_Date.Value%20%3D%20GetMemberData(1%2C%2016)%0A%20%20%20%20If%20GetMemberData(1%2C%2017)%20%3D%20%22Yes%22%20Then%0A%20%20%20%20%20%20%20%20Btn_Vote_Yes.Value%20%3D%20True%0A%20%20%20%20Else%3A%20Btn_Vote_No.Value%20%3D%20True%0A%20%20%20%20End%20If%0A%20%20%20%20If%20GetMemberData(1%2C%2018)%20%3D%20%22Yes%22%20Then%0A%20%20%20%20%20%20%20%20Btn_Photo_Yes.Value%20%3D%20True%0A%20%20%20%20Else%3A%20Btn_Photo_No.Value%20%3D%20True%0A%20%20%20%20End%20If%0A%20%20%20%20'%20Column%20S%20(19)%20%26gt%3BSpecial%20Needs%20%3D%20not%20used%0A%20%20%20%20I_Comment.Value%20%3D%20GetMemberData(1%2C%2020)%0A%20%20%20%20I_Film_Fee.Value%20%3D%20GetMemberData(1%2C%2021)%0A%20%20%20%20I_Film_Receipt.Value%20%3D%20GetMemberData(1%2C%2022)%0A%20%20%20%20'%20%20%20%20I_Film_Date_Paid.Value%20%3D%20GetMemberData(1%2C%2023)%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612589%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Excel%20Xlookup%20-%20How%20to%20use%20a%20Multi%20string%20lookup%20to%20find%20a%20record%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jan%2C%20thank%20you%20for%20your%20suggested%20code%20here.%26nbsp%3BUnfortunately%2C%20it's%20not%20a%20solution%20that%20will%20suit%20the%20end%20application%20in%20this%20instance%20as%20end-users%20of%20the%20forms%20wont%20have%20access%20to%20the%20spreadsheet%20itself.%20But%20I'm%20sure%20it%20will%20be%20useful%20for%20many%20others%3B%20and%20as%20I%20have%20only%20been%20programming%20for%20a%20few%20weeks%2C%20I%20have%20enjoyed%20the%20learning%20experience.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622807%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Excel%20Xlookup%20-%20How%20to%20use%20a%20Multi%20string%20lookup%20to%20find%20a%20record%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768560%22%20target%3D%22_blank%22%3E%40Debs_au%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20All%2C%20I%20have%20solved%20my%20problem%20with%20XLookup%20looking%20up%20more%20than%201%20string%20(ie%20look%20for%20both%20a%20given%20name%20and%20a%20surname%2C%20and%20return%20the%20record.%3C%2FP%3E%3CP%3EIf%20the%20given%20name%20is%20in%20cell%20A1%2C%20and%20the%20surname%20is%20in%20cell%20B1.%3C%2FP%3E%3CP%3EThe%20column%20to%20lookup%20to%20find%20the%20given%20name%20is%20Column%20F%3C%2FP%3E%3CP%3EThe%20column%20to%20lookup%20to%20find%20the%20surname%20name%20is%20Column%20G%3C%2FP%3E%3CP%3EAnd%20then%20return%20the%20whole%20record%20on%20the%20row%20(columns%20A%20to%20V)%20if%20there%20is%20a%20match%3C%2FP%3E%3CP%3EWith%20an%20error%20message%20%22Record%20Not%20Found%22%20if%20a%20match%20cant%20be%20found.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DXLOOKUP(A1%26amp%3BB1%2CF%3AF%26amp%3BG%3AG%2CA%3AV%2C%22Record%20Not%20Found%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20helps%20others%20with%20a%20similar%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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")

3 Replies
Highlighted
Best Response confirmed by Debs_au (Occasional Contributor)
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
Highlighted

@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.

Highlighted

@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.