What's better for this case, data model w/ power pivot, Access, or huge spreadsheet(s)?

Hi, I have a proble I am trying to solve and am hoping you can point me to the best way of doing this as it's costing soo many hours doing ot manually.

I have monthly billing files comprised of thousands of charts that we bill for which I am currently working to consolidate into one yearly file. From time to time we are asked to pull out a subset of this data either by a Chart ID or a list of invoice#s.

Problems:
The data is very large and over a year will exceed the million or so rows excel can display.
I know how to get this data into a data model or into Access, but I don't know how to pull only pieces of the data back out given a list of invoice#s or chart IDs. (for example we may have 1 million charts billed, but I need to pull out the detail for only a specific 1,500 or so charts.)

Hoping for some way to have a list of charts or invoice#s in another spreadsheet and basically say "Give me all the data in the YTD file that match this list of charts." etc.

Any idea how I could start to go about this? Currently we are having to open every single monthly file and manually pull out what we need with vlookups and filters, and then move to the next month and so on for a whole year. And we restart this process with a different list of charts next month. It's a monthly task. It's super painful.

Thanks!

Originally Posted by Pepe Le Mokko

Are you still using XL2007 as stated in your profile? Later versions might be better suited.
Please amend your profile if necessary. Thanks

Ah no that's outdated. I'll fix when I get back to my PC tonight. I have Office 365 desktop version.

Originally Posted by JohnTopley

Please provide a sample of your files with expected results. See yellow banner at top of page on how to attach a workbook(s).

Sure I'll try to mock something up when I get home. I can't share the real data. Privacy and all that. Thanks!