Compare a range of cells to a specific cell, if match, compare another cell on same row

Could someone please tell me if what I am trying to do is even possible. I have attached a spreadsheet as a reference.

Sheet 1 will have new names added on regularly and can be duplicates but will have different Accounts. I need the "Valid" column on sheet 2 to use a formula that will match A2 and B2 on sheet 2 to the correct row on Sheet 1 with the same information. Once the row is determined on sheet 1, to take the "Date" column C2 on sheet 2 and determine if is greater then or equal to sheet 1 column c of that row and less then or equal to sheet 1 column d of that row. If those statements are true, to insert "Active" in D2 on sheet 2 and "Inactive" if false.

Is this even possible??? Please help...I am completely stuck

If there will be only one unique name/dept combination in Sheet1, then you can use:

=IF(AND(C2>=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,B2),C2<=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2)),"Active","Inactive")

copied down.

I am not sure what you mean... If you filter by column A all the other data in the rows should hide/show simultaneously.

Can you show a sample workbook with some more data in it, and let us know what is going on.

I have attached my actual spreadsheet. I am trying to filter "Student Worker Pay" column A by A to Z but when I do that, the data in column D labeled "Valid" changes. The way it is attached is accurate but when I perform the filter, "Briana Ruiz" data changes. Does the same if I sort by Date, some of the data changes.

Am I doing something wrong.12-13 budget.xlsx

That's sorting not filtering...

Anyway, just take out all the sheetname references to the sheet you are in.. I.e. take out all the references to "Student Worker Pay"


=IF(A2="","",IF(AND(C2>=SUMIFS('Student Worker List'!C:C,'Student Worker List'!A:A,A2,'Student Worker List'!B:B,B2),C2<=SUMIFS('Student Worker List'!D:D,'Student Worker List'!A:A,A2,'Student Worker List'!B:B,B2)),"Active","Inactive"))

copied down.

Notice that I added a bit in front to clean up the column a bit...