Why is my Application.XLookup in VBA giving "Type Mismatch" Error?

Question

EDIT: Changed my code to use boolean logic instead of concatenating but am still getting the same error.

I am trying to use the application.xlookup in vba, and add extra criteria by concatenating the formula inside this code.

Sub employeelookup()
 SalesForm.BHSDEMPLOYEETD.Value = Application.XLookup(1, (Worksheets("TELEDATA").Range("E:E") = SalesForm.BHSDMAINNUMBERLF.Value) * (Worksheets("TELEDATA").Range("AI2:AI5") = SalesForm.BHSDRECORDTD.Value), Worksheets("TELEDATA").Range("F:F"))
End Sub

I have tried changing the parentheses and messing with the order but I was getting syntax errors. The code above should work in theory but I can't figure out where the Type Mismatch error is coming from.

Answer 1

You can put everything in arrays and loop those to find the value:

Sub employeelookup()
    Dim mn_num As Double
    mn_num = Val(SalesForm.BHSDMAINNUMBERLF.Value)
    Dim rec As String
    rec = SalesForm.BHSDRECORDTD.Value
    
    Dim lkpmnArr As Variant
    lkpmnArr = Intersect(Worksheets("TELEDATA").UsedRange, Worksheets("TELEDATA").Range("E:E")).Value
    
    Dim lkprecArr As Variant
    lkprecArr = Intersect(Worksheets("TELEDATA").UsedRange, Worksheets("TELEDATA").Range("AI:AI")).Value
    
    Dim lkotArr As Variant
    lkotArr = Intersect(Worksheets("TELEDATA").UsedRange, Worksheets("TELEDATA").Range("F:F")).Value
    
    Dim otpt As String
    otpt = "Not Found"
    
    Dim i As Long
    For i = LBound(lkpmnArr, 1) To UBound(lkpmnArr, 1)
        If lkpmnArr(i, 1) = mn_num And lkprecArr(i, 1) = rec Then
            otpt = lkotArr(i, 1)
            Exit For
        End If
    Next i
    
    
    SalesForm.BHSDEMPLOYEETD.Value = otpt
End Sub