IF & AND formula scenario with multiple criteria

Good Afternoon Everyone,

I have a spreadsheet that contains 4 lists that I am trying to pull information from 3of them in to the main list based on multiple criteria. I am having trouble with coming up with a formula to accomplish the task. The main list has over 50,000 rows and the others range from 6,000 - 8,000 rows each.

Attached is a simplified spreadsheet of what I am trying to accomplish. In column C I need to have the numbers from column I populate in column C based on the following criteria;

- if column A equals column F

- AND

- if column B is between columns G and H

- if both criteria are not met then the result would be "0"

I tried =IF((AND(A2:A7=F2:F4,B2>=G2,B2<=H2)),I2,0) but not getting the correct results. I was thinking I needed some kind of VLOOKUP included but can't wrap my head around it.

Any assistance would be greatly appreciated.

Thank you,

Charlie