You can't use the same formula, this is a trick formula designed to display the last value that matches all the criteria.

You'll need a different creature to work through all the variations and pull them back, an Array formula.

If you really want the LAST, then the SECOND LAST, etc, you use the LARGE function in the formula below. If you change your mind and decide you want to list them all starting from first to last, then change that to SMALL.

For reference:

F2: Dept2
G3: Dept1

Array formula:

F3: =INDEX($C$1:$C$8,LARGE(IF($B$1:$B$8=$E3,ROW($B$1:$B$8),""),COLUMN(A1)))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Now copy cell F3 and paste on G3.