「商品コードから商品名を引きたい」
「別の表から単価を持ってきたい」

Excelでもっとも使われるのが、この「探して取り出す」処理です。担当するのが検索・参照関数——VLOOKUP、XLOOKUP、INDEX＋MATCHなどのグループです。

ただ、種類が多くてどれを使えばいいのか分かりにくいのも事実です。

この記事では、やりたいこと別の早見表から入り、それぞれの使い分けと、つまずきやすいエラーの直し方までをまとめます。

コンテンツ
  1. まず結論：やりたいこと → 使う関数 早見表
  2. 結論から：いま選ぶなら XLOOKUP
  3. XLOOKUPが使えない環境なら INDEX＋MATCH
  4. MATCHの第3引数は必ず 0 にする
  5. INDIRECT：文字列からセル参照を作る
  6. よくあるエラーと直し方
  7. よくある質問（FAQ）
  8. まとめ

まず結論：やりたいこと → 使う関数 早見表

やりたいこと 使う関数 ひとこと
表から値を探して取り出す（定番） XLOOKUP 使えるならこれ一択
同上（古い環境） VLOOKUP 制約が多いが広く使われている
横方向に探したい HLOOKUP 使う場面は少ない
左方向にも探したい INDEX＋MATCH XLOOKUPが使えない環境の定番
何番目にあるか知りたい MATCH 位置（番号）を返す
位置を指定して取り出す INDEX MATCHと組で使う
番号で選択肢を切り替える CHOOSE 1なら春、2なら夏…など
行・列を入れ替える TRANSPOSE 縦の表を横にする
セル参照を文字列で作る INDIRECT シート名を可変にできる
基準からずらした位置を参照 OFFSET 動的な範囲指定に使う
セルの住所を文字列で得る ADDRESS INDIRECTと組み合わせる
行番号・列番号を得る ROW / COLUMN 連番作成などに便利

結論から：いま選ぶなら XLOOKUP

✅ Microsoft 365 / Excel 2021以降を使っているなら、XLOOKUPを覚えてください。
VLOOKUPの弱点がすべて解消されており、書き方もむしろ簡単です。新しく数式を書くなら、VLOOKUPを選ぶ理由はほとんどありません。

VLOOKUPの3つの弱点

弱点 XLOOKUPでは
左方向に探せない（検索値は必ず左端） 左右どちらでもOK
列番号を数える必要がある 範囲を指定するだけ。数えなくてよい
列を挿入すると壊れる（列番号がずれる） 壊れない

書き方の比較

「商品コードから商品名を引く」を両方で書いてみます。

VLOOKUP：=VLOOKUP(A2, 商品表!A:C, 2, FALSE)
XLOOKUP：=XLOOKUP(A2, 商品表!A:A, 商品表!B:B)

XLOOKUPは「何を探すか、どこから探すか、どこを返すか」の3つを書くだけです。最後の FALSE も、列番号の 2 も要りません。

さらに、見つからないときの値も引数で指定できます。

=XLOOKUP(A2, 商品表!A:A, 商品表!B:B, "該当なし")

IFERRORで囲む必要すらありません。

XLOOKUPが使えない環境なら INDEX＋MATCH

Excel 2019以前を使っている場合、XLOOKUPはありません。そのときの選択肢がINDEX＋MATCHです。

=INDEX(返したい範囲, MATCH(検索値, 探す範囲, 0))

具体例で見ます。

=INDEX(商品表!B:B, MATCH(A2, 商品表!A:A, 0))

役割を分けて考えると分かりやすい

  • MATCH … 「A2は、この範囲の何番目にある?」→ 番号を返す
  • INDEX … 「この範囲の○番目を取り出して」→ 値を返す

MATCHが探して番号を出し、INDEXがその番号の値を取り出す——2つで役割分担していると理解すれば、組み合わせが自然に見えてきます。

VLOOKUPと違って左方向にも探せます。XLOOKUPが使えない環境では、いまでも現役の手法です。

MATCHの第3引数は必ず 0 にする

⚠ MATCHの第3引数（照合の種類）を省略すると「1」になり、近似一致で動きます。
この場合データが昇順に並んでいないと、間違った結果を返します。しかもエラーになりません。
完全一致で探したいなら、必ず 0 を指定してください。

同じことがVLOOKUPの第4引数にも当てはまります。FALSE（または0）を忘れない——これが検索関数で最も多い事故の原因です。

INDIRECT：文字列からセル参照を作る

少し特殊ですが、覚えると便利なのがINDIRECTです。

=INDIRECT("A1")     → A1の値を返す
=INDIRECT(B1&"!A1") → B1に書いたシート名のA1を返す

シート名をセルで切り替えられるのが最大の利点です。月別シート（4月、5月…）が並んでいるブックで、「見たい月をセルに入力すると、その月のデータが表示される」といった仕組みが作れます。

ただし注意点があります。

  • 参照先のシート名が変わると壊れます（文字列なので追従しない）
  • 再計算が重くなりやすい
  • 閉じたブックは参照できません

便利ですが、使いどころを絞る関数です。

よくあるエラーと直し方

#N/A — 見つからない

検索・参照関数で最も多いエラーです。原因はほぼこの4つです。

  1. 本当に存在しない
  2. 余分なスペースが入っている（前後の空白）→ TRIM で除去
  3. 数値と文字列が食い違っている（「001」と 1 は別物）
  4. 完全一致の指定を忘れている（FALSE / 0 がない）

2番目と3番目は見た目で分からないため厄介です。=A2=B2 で比較すると、同じに見えて FALSE が返ることで気づけます。

詳しい対処はVLOOKUPで#N/Aが出るときの対処法にまとめています。

#REF! — 参照が壊れている

参照していた列や行を削除したときに出ます。VLOOKUPで列番号を指定している場合、列を挿入・削除すると番号がずれて誤った値を返すこともあります（エラーにならないので、こちらのほうが危険です）。

この問題が起きないのがXLOOKUPやINDEX＋MATCHです。列番号を使わない書き方に切り替えるのが根本的な対策になります。

エラーを隠すときの注意

IFERRORで囲めばエラーは消せますが、本当の間違いまで隠れます。「見つからない」だけを隠したいなら、IFNAのほうが安全です。

=IFNA(VLOOKUP(A2, 商品表!A:C, 2, FALSE), "該当なし")

詳しくはExcel情報関数まとめで解説しています。

よくある質問（FAQ）

VLOOKUPはもう使わなくていい?

新しく書くならXLOOKUPをおすすめします。ただし他の人と共有するファイルでは注意が必要です。相手がExcel 2019以前だとXLOOKUPは開けても再計算できません。共有先の環境を確認してから選んでください。

INDEX＋MATCHとXLOOKUP、どちらが速い?

大量データではINDEX＋MATCHのほうが速い場面もありますが、通常の業務データでは体感差はほとんどありません。読みやすさを優先してよいと思います。

Googleスプレッドシートでも同じ?

VLOOKUP・INDEX・MATCH・XLOOKUPはすべて使えます。加えてスプレッドシートにはQUERYという強力な関数があり、複雑な抽出はそちらのほうが簡単です。ExcelとGoogleスプレッドシートの違いもご覧ください。

複数条件で検索したい

XLOOKUPなら検索範囲を A:A&B:B のように結合し、検索値も同じ形にします。VLOOKUPの場合は、作業列で条件を結合しておくのが確実です。

まとめ

  • いま選ぶならXLOOKUP。VLOOKUPの3つの弱点（左に探せない・列番号を数える・列挿入で壊れる）がすべて解消されている
  • Excel 2019以前ならINDEX＋MATCH。MATCHが「何番目か」を探し、INDEXが「その番号の値」を取り出す
  • 完全一致の指定を忘れない。MATCHの第3引数は0、VLOOKUPの第4引数はFALSE。省略すると近似一致になり、エラーにならず間違った値が返る
  • #N/Aの原因はほぼ4つ——存在しない／余分なスペース／数値と文字列の食い違い／完全一致の指定漏れ
  • INDIRECTはシート名を可変にできるが、シート名の変更で壊れるため使いどころを絞る
  • エラーを隠すならIFERRORよりIFNA。本当のミスまで隠さないため

関連記事：Excel情報関数まとめ／Excel財務関数まとめ／Excelの数学・三角関数まとめ

Post Views: 29