Excel VBA: Xlookup + Multi-Criteria + Copy to Windows Clipboard

[zero269]

Hello,

I'm trying to get a VBA macro to COPY a value to the Windows Clipboard based on two Criteria.

I'm running a test using some sample data to match the Name and Library to a given Card number.

What I'm working with so far...
I've tested two different formula versions of using multi-criteria with xlookup and I get the correct return value.
However, after using these same two versions for VBA, neither are working for me.

VBA Testing.xlsm
	A	B	C	D	E	F	G	H
1	SAMPLE DATA				LOOKUP VALUES		RETURN VALUE
2	Name	Library	Card		Name	Library	Card
3	Student1	LAC	8235-48756-7917		Student4	LAPL	7778948142155	v1 - Multi-Lookup Criteria
4	Student1	LAPL	7412-87380-0240				7778948142155	v2 - Multi-Lookup Criteria
5	Student2	LAC	1343-93958-0255
6	Student2	LAPL	4087-11571-6391
7	Student3	LAC	6232-19248-3050
8	Student3	LAPL	4107-62169-7651
9	Student4	LAC	3216-95560-5077
10	Student4	LAPL	7778-94814-2155
11	Student5	LAC	6102-11202-2237
12	Student5	LAPL	2751-69146-4858
XlookupMulti

Cell Formulas
Range	Formula
G3	G3	=XLOOKUP(1,(t_LibraryCards[Name]=$E$3)*(t_LibraryCards[Library]=$F$3),t_LibraryCards[Card])
G4	G4	=XLOOKUP($E$3&$F$3,t_LibraryCards[Name] & t_LibraryCards[Library],t_LibraryCards[Card])

Here's the VBA Code I've been using to try and get the same results as the Formula in Excel:

'Copy Library Card Number
Sub Library_Card()
    
    'Data Types
    Dim CardName As String, CardLibrary As String, CardNum As String
    Dim LookupName As Range, LookupLibrary As Range, ReturnCard As Range
    
    'Table Ranges
    Set LookupName = Range("t_LibraryCards[Name]")
    Set LookupLibrary = Range("t_LibraryCards[Library]")
    Set ReturnCard = Range("t_LibraryCards[Card]")
    
    'Lookup Values
    CardName = Range("E3").Value
    CardLibrary = Range("F3").Value
    
    'Get Card Number
    
    'Using Single Criteria returns wrong match
    CardNum = Application.WorksheetFunction.Xlookup(CardName, LookupName, ReturnCard).Value
    
    'Using Multi-Criteria v1 returns Error: Type Mismatch
    CardNum = Application.WorksheetFunction.Xlookup(1, (LookupName = CardName) * (LookupLibrary = CardLibrary), ReturnCard).Value
    
    'Using Multi-Criteria v2 returns Error: Type Mismatch
'    CardNum = Application.WorksheetFunction.Xlookup(CardName & CardLibrary, LookupName & LookupLibrary, ReturnCard).Value

End Sub

Any help would be greatly appreciated. Thanks.

 

[Akuini]

Try it without .Value:

    'Using Single Criteria returns wrong match
    CardNum = Application.WorksheetFunction.XLookup(CardName, LookupName, ReturnCard)
    
    'Using Multi-Criteria v1 returns Error: Type Mismatch
    CardNum = Application.WorksheetFunction.XLookup(1, (LookupName = CardName) * (LookupLibrary = CardLibrary), ReturnCard)

 

[myall_blues]

I don't think you can join arrays/ranges like that. Try building the formula as a string and then evaluating it.

Sub Library_Card()
    
    'Data Types
    Dim strFormula As String
    Dim CardName As String, CardLibrary As String, CardNum As String
    'Lookup Values
    CardName = Range("E3").Value
    CardLibrary = Range("F3").Value
    '
    ' XLookup Formula
    '
    strFormula = "xlookup(""" & CardName & CardLibrary & """, t_LibraryCards[Name] & t_LibraryCards[Library], t_LibraryCards[Card])"
    
    'Get Card Number
    CardNum = Evaluate(strFormula)
    MsgBox CardNum
End Sub

 

[Alex Blakenburg]

I don't think you can join arrays/ranges like that. Try building the formula as a string and then evaluating it.

I quite liked @Eric W's wording in a couple of posts: "If you use WorksheetFunction, then everything inside it must be native VBA code, NOT spreadsheet formula syntax."

 

[zero269]

Try it without .Value:

    'Using Single Criteria returns wrong match
    CardNum = Application.WorksheetFunction.XLookup(CardName, LookupName, ReturnCard)
   
    'Using Multi-Criteria v1 returns Error: Type Mismatch
    CardNum = Application.WorksheetFunction.XLookup(1, (LookupName = CardName) * (LookupLibrary = CardLibrary), ReturnCard)

Hi Akuini,
Unfortunately, removing .value didn't resolve the issues. Turns out I was using the wrong logic to begin with.
I appreciate your time and effort; Thank you.

 

[zero269]

I don't think you can join arrays/ranges like that. Try building the formula as a string and then evaluating it.

Sub Library_Card()
  
    'Data Types
    Dim strFormula As String
    Dim CardName As String, CardLibrary As String, CardNum As String
    'Lookup Values
    CardName = Range("E3").Value
    CardLibrary = Range("F3").Value
    '
    ' XLookup Formula
    '
    strFormula = "xlookup(""" & CardName & CardLibrary & """, t_LibraryCards[Name] & t_LibraryCards[Library], t_LibraryCards[Card])"
  
    'Get Card Number
    CardNum = Evaluate(strFormula)
    MsgBox CardNum
End Sub

Hi myall_blues,
I ran your suggestion 'as is' and it returns the correct value.
However, I did run one minor change/experiment to combine two lines into one, and it too, worked.

CardNum = Evaluate("xlookup(""" & CardName & CardLibrary & """, t_LibraryCards[Name] & t_LibraryCards[Library], t_LibraryCards[Card])")

As for copying the Card Number to the Windows Clipboard, this one seems to be the simplest option I found a little earlier today:

'Copy Card Number to Windows Clipboard
CreateObject("htmlfile").parentwindow.clipboarddata.setdata "text", CardNum

Although I've seen the Evaluate used in other cases, I can't say I fully understood when to use it and not. This will definitely be something I implement in some of my other more overly complicated macros.
Thanks again myall_blues, I don't think this is the first time you've helped me with my Excel VBA deficiencies. It's very much appreciated.

 

[zero269]

I quite liked @Eric W's wording in a couple of posts: "If you use WorksheetFunction, then everything inside it must be native VBA code, NOT spreadsheet formula syntax."

Hi Alex,
I've taken that quote and saved it into my notes regarding the VBA xlookup function. This bit of information helps explain how I was going down the wrong path in the first place.
Thanks again…

 

[myall_blues]

Great news and thanks for the feedback.

I ran your suggestion 'as is' and it returns the correct value.
However, I did run one minor change/experiment to combine two lines into one, and it too, worked.

CardNum = Evaluate("xlookup(""" & CardName & CardLibrary & """, t_LibraryCards[Name] & t_LibraryCards[Library], t_LibraryCards[Card])")

The reason I like to put it in a string first is because it can be quite tricky to get it right, especially when building complex formulas that need embedded quotes or a lot of variable substitution. While developing the code I can display the string using debug.print until it’s correct, prior to trying to use it as a formula.