How to match data and then use the row of the match to start a new formula

[Dena]

Help!!!! Is there a formula that I can use that will find a match using sheet1 A:A to sheet2 A1 and if a match is found then takes sheet2 C1 and determines if it is greater than sheet1 column C of the row that had the match or less than sheet1 column D of the row that had the match. If it is, then return Active, if not, then return Inactive.

Is this even possible????