My knowledge of using Add-Ins is limited to my personal experiences and projects which may have different needs from yours. But here's my input.
What should exist in the Add-In and what should be in the workbooks?
You should use whatever code you feel is necessary and treat it as a regular module. Except I call the macros from within the Workbook because my Add-In code is particular to the Workbooks it modifies. So within the Workbook I use a conditional to check to make sure the Add-In is loaded, and if Yes then I call the Add-In code, if not then give a message box. I also pass ThisWorkbook to the Add-In so I can create an instance of it and its Sheets. I always create instances within the Add-In so things don't get messy.
Can/should I control formatting from the Add-In?
Of ranges and the Workbook? Doesn't matter in my case. I do it within the Add-In because I create instances of my objects any way, so it's easier to control and more robust. And because all the Workbooks are on the network with the Add-In, so if you aren't connected to the network you can't modify the Workbook any way. But if the Workbooks you plan on modifying are typically in other networks or stored locally then I would keep the formatting code within the Workbooks themselves.
Is it recommended to load and unload the add-in automatically when a workbook opening and closing a workbook?
For my purposes I don't close automatically, only open from the Workbook Open trigger. Users could have multiple workbooks which use the Add-In code, so if they have 2+ workbooks open I don't want to Add-In to close after they close one. I guess you could create logic to prevent that, but since my Add-Ins are called from Workbooks there's virtually no risk any way.
Multiple lookup tables and parameter values are shared between the workbooks, should/can store these values in the Add-In?
Not longterm, AFAIK. You could just have your Workbooks pass those arguments Add-In when you run your code though. Or have your code open the Workbook in question and pull information and close.