VBA for XLookup with multiple criteria for named ranges

Question

I'm trying to populate a userform text box using XLookup. The XLookup uses inputs in other textboxes in the form and references with excel tables. I got this working in the first example below which had only one lookup query, but I cannot get the second line with two queries working. I'm sure it's just some positioning of brackets and quotes, but can't fathom it. The lookup results should return a single cell. Any help appreciated. TIA

(This Works for simple lookup) 

Me.txtPrecinct.Value = Application.WorksheetFunction.XLookup(Me.cboName.Value, Range("DCA_LSP_Items[LSP_Name]"), Range("DCA_LSP_Items[Precinct]"))

(This Doesn't using the & for more than one query) 

Me.txtEscDays.Value = Application.WorksheetFunction.XLookup(Me.cboDCA.Value & Me.cboRevision.Value, Range("EngineTable[DCA]") & Range("EngineTable[Date]"), Range("EngineTable[Lots]"))

The code for the second one gets flagged when I run it. Tried all sorts of combos with brackets and quotes, but no luck so far.

Answer 1

Here's a basic example, following on from the post I linked above:

Sub Tester()
    
    Dim DCA, dt, frm As String
    
    DCA = "DCA001"   'Me.cboDCA.Value
    dt = "1/25/2022" 'Me.cboRevision.Value
    'basic formula template
    frm = "=XLOOKUP(1,(EngineTable[DCA]=""<DCA>"")*(EngineTable[Date]=DATEVALUE(""<dt>"")),EngineTable[Lots])"
    'replace formula placeholders with values from userform
    frm = ReplaceTokens(frm, "<DCA>", DCA, "<dt>", dt)
    
    Debug.Print frm '>>  =XLOOKUP(1,(EngineTable[DCA]="DCA001")*(EngineTable[Date]=DATEVALUE("1/25/2022")),EngineTable[Lots])

    'ideally use a specific worksheet instead of ActiveSheet
    Debug.Print ActiveSheet.Evaluate(frm) '>> 20
    
End Sub


'Replace tokens in `txt`: pass pairs of token+replacement values to `args()`
'Eg:  s = ReplaceTokens("Please {verb} this {noun}","{verb}","review","{noun}","report")
Function ReplaceTokens(txt As String, ParamArray args()) As String
    Dim i As Long, rv As String
    rv = txt
    For i = LBound(args) To UBound(args) Step 2
        rv = Replace(rv, args(i), args(i + 1))
    Next i
    ReplaceTokens = rv
End Function

My test table: