この記事で分かること
- VLOOKUPがモデル実務で嫌われる3つの理由（列番号・左参照・近似一致の罠）
- INDEX/MATCHの組み立て方と読み方——「どの行？どの列？」の分解思考
- XLOOKUPの基本と、実務モデルでの使い分け指針
なぜVLOOKUPを卒業するのか
- ①列番号がハードコード：=VLOOKUP(key, 範囲, 3, …)の「3」は、表に列を1本挿入した瞬間、黙って別の列を返します。エラーが出ない分だけ悪質で、モデルQC（4層チェック）でも捕まえにくい事故です。
- ②左方向を参照できない：検索キーより左の列は取れず、表の並びに設計が縛られます。
- ③第4引数の省略が近似一致：省略すると近似一致になり、ソートされていない表ではもっともらしい誤値を返します。完全一致のFALSE指定を忘れた新人の事故は定番中の定番です。
INDEX/MATCH：行と列を分けて考える
組み合わせの意味はシンプルです。MATCHが「何行目？」を探し、INDEXが「その行の値をこの列から取る」——場所探しと値取りの分業です。
| 数式 | 読み下し |
|---|---|
| =MATCH(A2, 商品コード列, 0) | A2のコードは商品コード列の何行目か（0＝完全一致。必ず0） |
| =INDEX(単価列, 行番号) | 単価列のその行番号の値を返す |
| =INDEX(単価列, MATCH(A2, 商品コード列, 0)) | 2つを合体：コードA2に対応する単価 |
- 列の挿入・移動に強い（列番号でなく列そのものを参照）、左方向も自由、処理も軽い——VLOOKUPの3欠点をすべて解消します。
- 行列両方を探す2次元参照 =INDEX(表, MATCH(行キー,…,0), MATCH(列キー,…,0)) は、年度×項目のマトリクスからの取り出しでモデル頻出です。
XLOOKUP：現代の標準
| 特徴 | 意味 |
|---|---|
| 既定が完全一致 | VLOOKUP最大の罠（近似一致既定）が解消されている |
| 戻り範囲を直接指定 | 列番号のハードコードが構造的に存在しない。左参照も自由 |
| 見つからない場合の指定 | IFERRORで包まず、第4引数で「該当なし」等を返せる（エラー隠蔽を避けられる） |
| 検索方向の指定 | 末尾から検索（最新レコードの取得）も1つの引数で可能 |
実務での使い分け指針
- 新規に作るならXLOOKUPが第一候補。ただし共有相手のExcelバージョンが古い環境では開けないため、配布先を確認してから使います（互換性が読めない配布物はINDEX/MATCHが安全）。
- 既存モデルの流儀に合わせる：チームのモデルがINDEX/MATCHで統一されているなら混ぜない。関数の混在は引き継ぎコスト（書式と作法）を上げます。
- 参照が壊れた時のチェック：#N/Aは「見つからない」の正直な報告です。空白や0で塗り潰さず、キー側の表記ゆれ（前後スペース・全半角）を先に疑うのが定石です（検算の設計はチェックの章）。
Q. まだVLOOKUPを使ってはいけないのですか？
A. 使い捨ての集計なら問題ありません。避けるべきは「長く使うモデル・他人が触るモデル」での使用です。列挿入で黙って壊れる関数は、共有資産の中では時限装置になります。
Q. MATCHの第3引数はなぜ毎回0と書くのですか？
A. 省略時は1（近似一致・昇順ソート前提）で、VLOOKUPと同じ罠があるからです。「MATCHの最後は必ず0」を指が覚えるまで書き続けるのが、事故率を下げる最短の訓練です。
まとめ
- VLOOKUP卒業の理由：列番号ハードコード・左参照不可・近似一致既定の3点。
- INDEX/MATCHは「何行目？」と「どの列から？」の分業。2次元参照までがモデルの基本装備。
- 新規はXLOOKUP（完全一致既定・戻り範囲直指定）、配布先の互換性だけ確認。流儀は混ぜない。
実務Excel教材（投資銀行フォーマット）
読んで分かったら、次は手を動かす番です。本記事の内容を実務形式のExcelで組み上げるための教材を用意しています。
本記事について
本文中の数値例はすべて理解のための設例であり、実在の企業・案件とは関係ありません。制度・実務慣行に触れる箇所は一般的傾向の整理です。個別案件への適用時は必ずその時点の一次情報・専門家にご確認ください。