Excel Formula with Logic Needed

[Panama1182]

I need an excel formula with proper logic to copy the value of the cell above given certain criteria. Cell A377-Cell A747 needs to equal cell A376. Cell A748 is a different name but should stay that way because Cell C748 reads "Department 000." Essentially I want a formula that can understand that anytime it reads "Department 000" in Column C then the name in Column A is correct. For every cell below "Department 000" through "All Department Num" the correct value in Cells A should be the name of the closest cell above them where Column C reads "Department 000."

 

[Panama1182]

Just out of curiosity what does the "/" do in the formula?

 

[Cubist]

It's a division sign.
The formula checks if the row has "Department 000" with $D$4:D4="Department 000". This returns TRUE or FALSE (1 or 0).
If TRUE, then 1/1 =1. If FALSE, then 1/0= #DIV0!
Lookup returns the closest match. Here, 2 is typically chosen as an arbitrary number that is close to 1, but it can be anything close to 1 like 1.1, 1.5, etc..
Finally, return the corresponding last TRUE in column B.