Vlookup/Index search in large woorksheet

[Hjulben]

I have a range of prices I need to lookup in matching pricelists (for a price check on approx 10.000 invoices). Normally I would just do a vlookup, where I search for the item numbers in a pricelists, which gives me the agreed price for a comparison. But now I have to do a lookup in a pricelist that has more than 4 million item numbers, which means, that it is split between several worksheets. What would be the best way to do said lookup?

 

[mart37]

Maybe PowerPivot?

 

[pocquet]

Sounds like you are dealing with a fairly large dataset.

Excel will most likely die or take forever doing a lookup/match on such a large amount of rows and columns.

I would look into using a database (MS Access would be more efficient than excel if you have no other DBMS available) and using that to query the results over excel.

Using Access will make things faster, more efficient and remove allot of margin of error (eg borked formulas).