Xlookup with Multiple Criteria using Excel VBA

Question

In Excel, you can have multiple criteria when using the Xlookup feature.

A "normal" Xlookup looks something like this:

=xlookup("ThingToLookFor", "Search Range", "Return Range")

A multiple criteria Xlookup looks something like this:

=xlookup("ThingToLookFor" & "OtherThingToLookFor", "Search Range 1" & "Search Range 2", "Return Range")

I'm trying to do a multiple criteria Xlookup in VBA. Using & combines the two strings together, so that's no good.

What is the correct version of this in VBA?

WorksheetFunction.Xlookup("ThingToLookFor" & OtherThingToLookFor", "Search Range 1" & "Search Range 2", "Return Range")?

Note: I did discover "Evaluate". I can make it work, but I'm not sure I like it.

A more specific example:

Sub xlookup_test()

    Dim Lookup_Value_1 As String
    Lookup_Value_1 = "My Document"
    
    Dim Lookup_Value_2 As String
    Lookup_Value_2 = "Sales"
    
    Dim Search_List_1 As Range
    Set Search_List_1 = Document_Control.Range("DC_Document_Type")
    
    Dim Search_List_2 As Range
    Set Search_List_2 = Document_Control.Range("DC_Document_Name")
    
    Dim Return_List As Range
    Set Return_List = Document_Control.Range("DC_Document_ID")
    
    Dim Return_Value As String

    ' this is the problem line    
    Return_Value = WorksheetFunction.XLookup(Lookup_Value_1 & Lookup_Value_2, Search_List_1 & Search_List_2, Return_List)
    
    Debug.Print (Return_Value)
    
End Sub

However, as mentioned before, using the & is just combining the two strings together to make one string instead of telling it that it's two different things it needs to look for.

Please provide an example. See How to create a Minimal, Complete, and Verifiable example — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 7, 2021 at 11:26
Alright, so the two named ranges I'm searching through are "List_Document_Name" and "List_Document_Type". The named range I want to return from is "List_Document_ID". (Apparently pressing enter submits the comment. Oops. Anyway...) Let's say the document name I'm searching for "My Document" and the document type I want is "Sales". — Benjamin Walker
– Benjamin Walker, Commented Sep 9, 2021 at 18:07
Please edit your question to provide examples of your data. I'm sure your description in your comment makes sense to you, but an example that can be copy/pasted into a worksheet, along with your expected output given the input, would make things much easier for others to help you. — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 9, 2021 at 18:14
Alright, I updated it. I did have a better example writeup in the previous comment but I kept pushing enter and submitting it by mistake and then it wouldn't let me edit it anymore. First time posting here so still figuring it out a little bit. — Benjamin Walker
– Benjamin Walker, Commented Sep 9, 2021 at 18:50
I still don't see a data example I can copy/paste into my worksheet, nor an example of what the results should look like. — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 9, 2021 at 19:25

Gravitate · Accepted Answer · 2021-09-07 08:19:16Z

1

Unfortunately, I don't have access it XLOOKUP in my version of Excel, so I cannot test. But I think the issue is your use of quotes (") around your search ranges.

You say: "Using "&" combines the two strings together". That is exactly what is happening, except that you are combining the strings OF the addresses, rather than the strings AT those addresses.

Try this (you will need to adjust the search ranges and criteria to suit):

WorksheetFunction.Xlookup("ThingToLookFor" & "OtherThingToLookFor", A1:A3&B1:B3, C1:C3)

If "ThingToLookFor" & "OtherThingToLookFor" are also cell references, remember to remove the " from those too.

answered Sep 7, 2021 at 8:19

Gravitate

3,0722 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Xlookup with Multiple Criteria using Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related