@ThinhDinh did an excellent tutorial on using arrayformulas. It’s almost a year old, but I think most of it is still relevant.
Arrayformulas are very useful, but as the capabilities of Glide are being extended, they are becoming less necessary. Many things that used to require an arrayformula can now be done natively in Glide using computed columns. This is much preferable, as calculations happen on a user device instantly, and delays caused by syncing with the Google Sheet are eliminated.
My general rule of thumb when it comes to formulas is to first look for a way to do it in Glide, and only turn to a spreadsheet formula (or script) as a last resort.
Applying the above to your scenario, you could have achieved the same thing using a relation and a lookup column 