Formula question - how can i pull the data i am looking for?

I have an excel report that shows account numbers and a subtotal for the department. Each department has the same account number, so the detail looks like this:


Account 0001
Account 0002
Account 0003
Department 1234 subtotal


Account 0001
Account 0002
Account 0003
Department 5678 subtotal


I am trying to use a formula to pull the amount for account 0002 for department 5678, but I am not sure how to do this without doing a 'sumif' and picking up all account 0002 instances, or by manually adding the department number to the account number so that I have a unique identifier. there are 60k rows of data, so combining the account number and department number would be time consuming. Is there an easier formula that would pull the balance for account 0002 in department 5678 on a monthly basis, when there may be accounts or departments added so the rows change?

Is there a way to create a formula that looks for 'department 5678 subtotal' and then pulls the data for the first 'account 0002' preceding it?

Attached is an example. Each month, I would pull the same information on the "after" tab that is in yellow, however the data is downloaded from PeopleSoft so there may be additional rows of data added each month.

Do the number of accounts vary by month and/or department?

They do change, there may only be 1-2 new accounts added in a month, but it would be added for each department, so the impact on the report may be about 10-12 new lines.