The updated formula is working for me now, thanks!
This formula will break with any Transactions sheet row insertion operations above row 2, including Manual Transaction. For example, the D2:D range will shift to D3:D after one row insertion.
One option would be to wrap D2:D inside an INDIRECT string: INDIRECT("D2:D")
={"Type";ARRAYFORMULA(XLOOKUP(INDIRECT("D2:D"),Categories!A:A,Categories!C:C,""))}
Or, some other options that use the full column D:D range, similar to these that you may recognize 
=ARRAYFORMULA(IF(ISTEXT(D:D),XLOOKUP(D:D,Categories!A:A,Categories!C:C,""),))
=ARRAYFORMULA(XLOOKUP(D:D,Categories!A:A,Categories!C:C,""))
=ARRAYFORMULA(IF(ROW(D:D)=1,"Type",XLOOKUP(D:D,Categories!A:A,Categories!C:C,"")))