Custom order (not alpha or numeric) of query results

Is it possible to create a custom ORDER BY function in a google sheets QUERY?

In this scenario, I don't want the results to be alphabetical nor numeric, but rather a custom order based on a predefined category list that is actually in a separate tab from both the QUERY range as well as the formula itself.

I'm not really sure where to begin: Is this even possible? Should the category list be moved to one of the other sheets? Does the category list need to be written into the formula?

Just looking for guidance at this point.

My Sheet