Syntax in Excel VBA: how to refer to column of a table as a range in the "=IF" function

I'm trying to find the row of a cell (in a table) which meets 3 criteria. Thanks to another post (VBA (Excel): Find Based on Multiple Search Criteria Without Looping) I have come up with this working code:

x = Filter(Application.Transpose(Application.Evaluate("=IF((Sheet6!A6:A15=""joseph"")*(Sheet6!B6:B15>date(2001,1,1))Sheet6!C6:C15=""grenade""),ROW(Sheet6!A6:A15),""x"")")), "x", False)

I would now like to use named ranges in this code instead of "Sheet6!A6:A15). When I try this I keep getting an error "Type mismatch". I have tried to set different ranges such as:

1) Dim rng1 As Range
   Set rng1 = Worksheets("Sheet6").Range("A7:A15")
   x = Filter(Application.Transpose(Application.Evaluate("=IF((rng1=""joseph"")*(Sheet6!B6:B15>date(2001,1,1))*Sheet6!C6:C15=""grenade""),ROW(rng1),""x"")")), "x", False)*

2) Dim rng2 As Range
   Set rng2 = Worksheets("Sheet6").Range("TestTable10[Column1]")
   x = Filter(Application.Transpose(Application.Evaluate("=IF((rng2 =""joseph"")*(Sheet6!B6:B15>date(2001,1,1))*Sheet6!C6:C15=""grenade""),ROW(rng2 ),""x"")")), "x", False)*

3) Dim rng3 As Range
   Set rng3 = Worksheets("Sheet6").ListObjects("TestTable10").ListColumns(1).Range
   x = Filter(Application.Transpose(Application.Evaluate("=IF((rng3 =""joseph"")*(Sheet6!B6:B15>date(2001,1,1))*Sheet6!C6:C15=""grenade""),ROW(rng3 ),""x"")")), "x", False)*

I have also tried by doing the same for the ranges B6:b15 and C6:C15 but no matter what I do, I keept getting an error "Type Mismatch"

Thank you for your help!