XLOOKUP関数を使っていて、「#N/A」や「#VALUE!」などのエラーに悩まされていませんか？
特に急いでいるときにこのエラーが出ると、イライラしますよね…。何度やっても直らないと、つい画面を叩きたくなる気持ちも分かります😅
でも、ちょっと待ってください！
この記事を読めば、XLOOKUPの代表的なエラーの原因と解決策がすぐに分かります。
さらに、XLOOKUPの代替手段としてINDEX+MATCHを活用する方法も紹介するので、ぜひ最後までチェックしてください。
✅ XLOOKUPで発生する代表的なエラーと解決策一覧
まずは、XLOOKUPでよく発生するエラーの種類とその原因・解決策を一覧表で整理しました。
| エラーコード | 原因 | 解決策 |
|---|---|---|
#N/A |
検索値が見つからない | XLOOKUPの4番目の引数を指定 or IFNA 関数を使用 |
#VALUE! |
範囲のサイズが違う | 検索範囲と戻り範囲のサイズを統一 |
#REF! |
参照範囲が削除された | 正しいセル範囲を指定 |
❶ #N/A エラーの原因と解決策
🔍 #N/A エラーの原因
- 検索値が見つからない場合、XLOOKUPは
#N/Aエラーを返します。
💡 解決策：見つからないときの処理を設定する
XLOOKUPには、**「検索値が見つからない場合のデフォルト値」**を設定できる4番目の引数があります。
この引数を活用すれば、エラーではなく「データなし」と表示できます。
✅ 例:
=XLOOKUP(105, A2:A4, C2:C4, "データなし")
👉 検索値 105 が見つからない場合、「データなし」と表示される
✅ 別の方法：IFNA関数を使う
XLOOKUPの代わりに IFNA 関数を使うこともできます。
=IFNA(XLOOKUP(105, A2:A4, C2:C4), "データなし")
💡 IFNA関数を使うと、他の関数とも組み合わせやすいので便利です！
❷ #VALUE! エラーの原因と解決策
🔍 #VALUE! エラーの原因
- 検索範囲と戻り範囲のサイズが異なる
- 検索値が配列で渡され、戻り範囲が対応していない
💡 解決策：範囲サイズを統一する
✅ エラーが発生する例（範囲サイズが異なる）
=XLOOKUP(A2, B2:B10, C2:C5) ❌ （戻り範囲の行数が異なる）
✅ 正しい例（範囲サイズを統一）
=XLOOKUP(A2, B2:B10, C2:C10) ✅
💡 検索範囲と戻り範囲のサイズは必ず揃えましょう！
❸ #REF! エラーの原因と解決策
🔍 #REF! エラーの原因
- 参照先のセルや範囲が削除されている
- XLOOKUPの検索範囲や戻り範囲に無効なセルが指定されている
💡 解決策：正しいセル範囲を確認する
- 範囲が削除されていないか確認する
- 数式の中で
#REF!が出ているセルを特定する - 範囲を再指定する
❹ XLOOKUPが遅いときの対策（INDEX+MATCHで高速化）
「XLOOKUPが遅い…」と感じたことはありませんか？
XLOOKUPは非常に便利ですが、データが数万行以上あると、動作が遅くなることがあります。
そんなときは、INDEX+MATCH を使うと高速化できることがあります。
❺ INDEX+MATCH の使い方（XLOOKUPの代替手段）
🔹 INDEX+MATCH の基本
✅ 公式：
=INDEX(戻り範囲, MATCH(検索値, 検索範囲, 0))
📌 例：商品コードから価格を取得する
| A列 (商品コード) | B列 (商品名) | C列 (価格) |
|---|---|---|
| A101 | りんご | 100 |
| A102 | みかん | 120 |
| A103 | バナナ | 150 |
| A104 | ぶどう | 200 |
✅ A103 の価格を取得する
=INDEX(C2:C5, MATCH("A103", A2:A5, 0))
👉 結果：150
❻ INDEX+MATCH を VLOOKUP と比較
| 項目 | INDEX+MATCH | VLOOKUP |
|---|---|---|
| 検索方向 | どの列でもOK | 左端の列のみ |
| 速度 | 大量データで高速 | 遅くなることがある |
| 追加列の影響 | 影響なし | 列を追加すると崩れる |
| Excelの対応バージョン | すべてのバージョン | すべてのバージョン |
✅ XLOOKUPが使えないExcel（2016, 2013, 2010など）でも、INDEX+MATCHを使えばOK！
❼ INDEX+MATCHで複数条件検索（応用編）
📌 例：商品コードと色で価格を取得する
| A列 (商品コード) | B列 (色) | C列 (価格) |
|---|---|---|
| A101 | 赤 | 100 |
| A101 | 青 | 110 |
| A102 | 赤 | 120 |
| A102 | 青 | 130 |
✅ 「A101」かつ「青」の価格を取得
=INDEX(C2:C5, MATCH(1, (A2:A5="A101") * (B2:B5="青"), 0))
👉 結果：110
💡 Excel 2016以前のバージョンでは、CTRL+SHIFT+ENTER で配列数式として確定する必要があります！
❽ まとめ
✅ XLOOKUPのエラー対策まとめ
#N/A→ 4番目の引数 or IFNA関数で対応#VALUE!→ 検索範囲と戻り範囲のサイズを統一#REF!→ 削除されたセル範囲を修正
✅ XLOOKUPが遅いときは INDEX+MATCH を活用すると高速化できる！
👉 Excelの検索機能をより効果的に使うために、XLOOKUPとINDEX+MATCHをマスターしましょう！ 🚀
Follow me!
コメント