数十万件のデータをExcelで扱っていて、どうにも重い、メモリ不足で落ちる、といったことがありました。
ググると「データを減らす」という対応法が載っていますが、数十万件のデータを一度に扱わないといけなかったのでこれは困難でした。
また、データをただ入力して関数で計算していただけなので、条件付き書式等の高尚なことはしていませんし、見えない所にゴミデータがあるということもありませんでした。
色々試行錯誤した結果、以下の２つが効果があったので紹介します。
１．シートを分ける代わりにファイルを分ける
何段階かに分けて処理を行っていたため段階毎にシートを分けていたのですが、シートで分ける代わりにファイル（ブック）で分けたら一気に処理が軽くなりました。
理由は後で調べたのですが、どうやらExcelはファイル毎にプロセスが分かれているみたいです。ファイルで分けることでマルチプロセス状態となったために、処理速度が向上しました。
なお、Excel2010までは意識せずともマルチプロセスになるのですが、Excel2013やExcel2016の場合は「ファイル名を指定して実行」で「excel /x」というコマンドを与えないとマルチプロセスにならないようなので注意が必要です。
２．Excel以外のプログラムを使って対応
これを言ってしまったら元も子もないのですが、現実問題としてExcel以外のプログラムで処理できることはExcel以外のプログラムで処理した方が良いです。
例えば、ソート・マージ、区切り文字や改行コードを使った簡単なフォーマットの変更、といった作業なら、サクラエディタを使った方が断然早いです。
IT業界に勤めているのであれば、場合によってはjava等で簡単なプログラムを作っても良いかもしれません。
なお、vlookup関数の高速化でググったら、「オープンソースのRDBMSを使えば高速で処理できるようになるよ！」という（ちょっとワイルドな）例もありました。
