For a long time I've been trying to figure out why you cannot do a look-up from site A to site B. Instead of a diverse and organized site collection with hubs and sub-sites like MS advocates you do, this forces you to put everything into one site. There are a number of threads related to this topic that have never really been answered, here is one:

https://learn.microsoft.com/en-us/answers/questions/57439/display-list-from-another-site-collection

As an example, when making invoices, it would be great to have a look-up column to a master product list. Problem is, if the master product list resides on an operations site instead of the accounting site, you cannot do this! Very frustrating that the only solutions to this seem to involve complex work-arounds, but I've done enough work with some of the custom work-arounds to know it is possible and MS could make this work.