Help with VLOOKUP if a date is between two dates

In a nutshell, what I want is: In Sheet 2, return by vlookup the information in Sheet 1 RC1:100 if the date on Sheet 1 in RC1 is between the two different dates on Sheet 2 (for example in RC4 and RC7). ...My workbook is a budget sheet that has a list of the debts on Sheet 1 with the standard due dates and amounts due. Sheet 2 is the actual breakdown and scheduling, week by week, of what has come due, according to the due dates. Please note Sheet 1 is able to be changed, as necessary, at any time. Sheet 2 is where those changes will materialize as a plan. Is there a way to say "bring this information over IF this date is BETWEEN these two dates"? I'm assuming I need an IF AND statement with a VLOOKUP, but the IF AND part isn't working for me, or I'm doing it wrong. I've not posted on the forum before, so if there is something else that is generally mentioned that I am not, please let me know. Thanks...

I think it would be an array formula like

=IFERROR(INDEX(Sheet1!M1:M100,MATCH(1,(Sheet1!A1:A100>=D2)*(Sheet1!A1:A100<=G2),0)),"")

It is generally a good idea to include a sample workbook with dummy data for what you are trying to accomplish.
In this case have sheet1 with dummy data in the format you have, and sheet2 with what you expect the results to look like.
I know that what you are trying to do can be done with VBA. To do this with a formula will take some work, possibly some extra columns for additional checks.