My customer sends me 100 - 200 orders a day using an EDI tool, but also email me a "Template" which lists around 2000 of his most recent orders (some of which we already delivered), and I have to send it back to him with status and product information about each order.   The status code is contained in a huge 20,000 row table that I refresh regularly from the server.   Easy with a lookup function.  Then I use a lookup from a third table to translate the status code into plain engliish the customer can read, and finally a 4th table to lookup some comments which apply to specific item references.  Historically I've done this with VBA & lindex match (or in later versions with xlookups), but it is not reliable because the data is often a bit messy. I tried creating a data model but came up with error messages when I tried to establish connections, I think because the data is messy - and anyway I'm not sure Power Pivot and data models is the way to go because I dont want output as a Pivot Table, I just need to bring all the data back to the Template table and done.

All the "help" I can find seems to be about appending data all of similar format (which this isnt) , or merging to create Pivot Tables.

Am I missing something really obvious ?