VLOOKUP with date range does not work as intended

I have two excel tables in separate sheets:

table1:

Site_ID ....... Visit_Date
--------------------------
AS01            12-Feb-23
ES96            10-Jan-23   
GH15            14-Mar-23
AS01            26-Mar-23
VD10            08-Apr-23
LS18            14-Jan-23
GH15            26-Mar-23

table2:

            01-Feb-23   01-Mar-23   01-Apr-23
            28-Feb-23   31-Mar-23   30-Apr-23
Site ID         FEB23       MAR23       APR23
---------------------------------------------
AS01
ES96
GH15
VD10
LS18

I want to lookup for Site_ID in table1 and insert Visit_Date in table2 if this date is in proper range listed above table2 header.

I've tried using this formula:

=VLOOKUP(table2[@[Site ID]:[Site ID]],IF((table1[[Visit_Date]:[Visit_Date]]>=E$1)*(table1[[Visit_Date]:[Visit_Date]]<=E$2),table1[[Site_ID]:[Visit_Date]],""),12,FALSE)

but, unfortunately it returns dates that are not in range in FEB23 column and in rest of the columns it returns value error. What am I doing wrong?

Solution

You may try the following formula-

=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$8,($A$2:$A$8=$E5)*($B$2:$B$8>=F$2)*($B$2:$B$8<=F$3),""),"dd-mmm-yyyy"))