IF & AND formula scenario with multiple criteria

New Contributor

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


- 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,


2 Replies
best response confirmed by allyreckerman (Microsoft)


In C2:


Adjust the ranges for your actual setup, then fill down.

@Hans Vogelaar 


Thank you! Worked perfectly!