Good Morning, I have two tables of data in Table 1 in column D I have 430,000 rows of data and in table 2 in column C I have 350,000 rows of data. completing a Lookup is crashing the computer, Can anyone in the room offer any alternative formula please.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Best Lookup option for two tables with 400,000 rows of data
- Thread starter dhome
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,265
- Office Version
- 365
- Platform
- Windows
The best solution is probably NOT Excel.
What you are describing is really a large relational database. While you may be able to brute-force Excel to try to behave like a relational database, that is NOT what it was designed for, so it typically does not handle it to efficiently.
You would be better off trying a database solution, such as Microsoft Access or SQL. If those are not possibilities, then I would recommend investigating trying to use Power Pivot, which allows you to do database-like functions in Excel. We have a separate forum for help on the Power Tools (appropriately called "Power Tools"). There are some Stickys at the top of that forum with some information on those tools.
What you are describing is really a large relational database. While you may be able to brute-force Excel to try to behave like a relational database, that is NOT what it was designed for, so it typically does not handle it to efficiently.
You would be better off trying a database solution, such as Microsoft Access or SQL. If those are not possibilities, then I would recommend investigating trying to use Power Pivot, which allows you to do database-like functions in Excel. We have a separate forum for help on the Power Tools (appropriately called "Power Tools"). There are some Stickys at the top of that forum with some information on those tools.
Upvote
0
Hi Joe
Your answer is what I expected. Eventually, the LOOKUP would be automated as the data tables are delivered via large downloads, so thinking Access with a query written may be the option but I'll take a look at Power Tools also.
Thanks for your advice
David
Upvote
0
Similar threads
- Question
- Solved
