【Excel】XLOOKUP関数の「見つからない場合」引数を活用!Excelのエラー表示を自在に制御する方法

Excelでデータを検索する際、一致する値が見つからないと#N/Aエラーが表示されます。このエラーは、計算結果を誤らせたり、見た目を損ねたりする原因となります。特に、複数の関数を組み合わせる場合や、集計表でエラーが混じると、原因特定が困難になることもあります。XLOOKUP関数は、この「見つからない場合」の処理を引数で直接指定できるため、エラー表示を自在に制御できます。

この記事では、XLOOKUP関数の「見つからない場合」引数の使い方を解説します。エラー表示を回避し、より見やすく、正確な表を作成するための具体的な方法を習得できます。

XLOOKUP関数の「見つからない場合」引数とは

XLOOKUP関数は、指定した範囲で検索値と一致する値を探し、対応する範囲から値を取り出す関数です。この関数には、検索値が見つからなかった場合に返す値を指定できる「見つからない場合」という引数があります。この引数を活用することで、Excel標準のエラー表示である#N/Aを、任意の文字列や数値に置き換えることが可能です。

これにより、データ検索の結果が#N/Aで表示されるのを防ぎ、表全体の可読性を向上させることができます。例えば、在庫リストから商品コードを検索し、該当する商品がない場合に「在庫なし」と表示させる、といった応用が可能です。

XLOOKUP関数で「見つからない場合」を制御する手順

XLOOKUP関数の「見つからない場合」引数は、関数の5番目の引数として指定します。この引数は省略可能ですが、エラー表示を制御したい場合には必ず設定します。

1. 検索対象のセルを選択する
   XLOOKUP関数を入力したいセルを選択します。
2. XLOOKUP関数を入力する
   数式バーに「=XLOOKUP(」と入力し、検索値、検索範囲、結果範囲を指定します。
3. 「見つからない場合」引数を指定する
   指定したい文字列や数値を、検索範囲と結果範囲の後にカンマで区切って入力します。例えば、「”該当なし”」や「0」を指定できます。
4. 閉じ括弧を入力してEnterキーを押す
   数式を完成させ、結果を確認します。

「見つからない場合」引数の具体的な活用例

「見つからない場合」引数は、様々な状況で役立ちます。ここでは、具体的な活用例をいくつか紹介します。

例1:該当データがない場合に「0」を表示する

商品IDを検索し、該当する商品がない場合に売上数量として「0」を表示したい場合です。IFNA関数などを使わずに、XLOOKUP関数だけで完結できます。

数式例:

=XLOOKUP(A2,商品リスト!$A:$A,商品リスト!$B:$B,”0″)

この数式は、セルA2の商品IDを「商品リスト」シートのA列で検索し、B列の売上数量を返します。もし商品IDが見つからなかった場合は、「0」を表示します。

例2:該当データがない場合に特定のメッセージを表示する

顧客IDを検索し、存在しない場合に「顧客情報なし」と表示したい場合です。メッセージはダブルクォーテーションで囲む必要があります。

数式例:

=XLOOKUP(B2,顧客リスト!$A:$A,顧客リスト!$C:$C,”顧客情報なし”)

セルB2の顧客IDが「顧客リスト」シートのA列で見つからない場合、「顧客情報なし」という文字列が返されます。

例3:該当データがない場合に空白を表示する

検索結果が#N/Aになるのを完全に避け、セルを空白にしたい場合です。ダブルクォーテーションを2つ並べることで、空白文字列を指定できます。

数式例:

=XLOOKUP(C2,製品コード!$A:$A,製品コード!$D:$D,””””)

セルC2の製品コードが「製品コード」シートのA列で見つからなかった場合、セルは空白になります。

例4:IFNA関数との組み合わせ(より複雑なエラー処理)

XLOOKUP関数単体では対応できない、より複雑なエラー処理を行いたい場合は、IFNA関数と組み合わせることも可能です。例えば、XLOOKUP関数が#N/Aを返した場合に、さらに別の検索を実行する、といったシナリオです。

数式例:

=IFNA(XLOOKUP(D2,リストA!$A:$A,リストA!$B:$B,”見つかりません”),XLOOKUP(D2,リストB!$A:$A,リストB!$B:$B,”それでも見つかりません”))

この例では、まずリストAで検索し、見つからなければ「見つかりません」と表示します。それでも見つからなかった場合、IFNA関数がそれを検知し、次にリストBで検索を実行します。最終的にリストBでも見つからなければ「それでも見つかりません」と表示します。

XLOOKUP関数とIFNA/IFERROR関数の違い

Excelには、#N/Aエラーを処理するためのIFNA関数や、あらゆるエラーを処理するためのIFERROR関数も存在します。XLOOKUP関数の「見つからない場合」引数との主な違いは、エラー処理の範囲と記述の簡潔さにあります。

IFNA関数

IFNA関数は、数式の結果が#N/Aエラーの場合に、指定した値を返します。それ以外のエラー(#VALUE!、#DIV/0!など)はそのまま表示されます。XLOOKUP関数の「見つからない場合」引数は、#N/Aエラーに限定して値を返すため、IFNA関数と似た動作をします。しかし、XLOOKUP関数では「見つからない場合」の指定が関数の一部として組み込まれているため、別途IFNA関数を記述する必要がありません。

IFERROR関数

IFERROR関数は、数式の結果が「エラー」全般(#N/A、#VALUE!、#DIV/0!など)の場合に、指定した値を返します。XLOOKUP関数の「見つからない場合」引数は、あくまで「見つからない」という条件に特化しており、他の種類のエラーには対応していません。そのため、他のエラーもまとめて処理したい場合は、IFERROR関数の方が適しています。しかし、XLOOKUP関数で#N/Aエラーを処理したいだけであれば、「見つからない場合」引数を使う方がコードが短くなり、意図も明確になります。

使い分けのポイント

XLOOKUP関数の「見つからない場合」引数は、#N/Aエラーのみを処理したい場合に最も効率的です。IFNA関数は、XLOOKUP関数以外の関数で#N/Aエラーを処理したい場合に有用です。IFERROR関数は、#N/A以外のエラーも含め、あらゆるエラーをまとめて処理したい場合に適しています。目的に応じてこれらの関数や引数を使い分けることが重要です。

XLOOKUP関数「見つからない場合」引数の制限事項と注意点

XLOOKUP関数の「見つからない場合」引数は非常に便利ですが、いくつかの制限事項と注意点があります。これらを理解しておくことで、意図しない結果を回避できます。

1. #N/Aエラー以外のエラーには対応しない

この引数は、あくまで検索値が見つからなかった場合に発生する#N/Aエラーのみを処理します。例えば、検索範囲や結果範囲の指定が間違っている場合などに発生する#VALUE!エラーや#REF!エラーなど、他の種類のエラーは「見つからない場合」引数では処理されません。これらのエラーが発生した場合は、別途IFERROR関数などを使用するか、数式の誤りを修正する必要があります。

2. 検索範囲・結果範囲のサイズは一致させる必要がある

XLOOKUP関数では、検索範囲と結果範囲のサイズ(列数または行数)が一致している必要があります。もしサイズが異なると、#VALUE!エラーが発生します。これは「見つからない場合」引数とは直接関係ありませんが、XLOOKUP関数を使用する上での基本的な制約です。エラーを避けるため、常に両者のサイズを確認してください。

3. Excelのバージョンによる利用可否

XLOOKUP関数は、比較的新しい関数です。Excel for Microsoft 365、Excel 2021、Web版Excelなどで利用可能ですが、Excel 2019以前のバージョンでは利用できません。古いバージョンのExcelを使用している場合は、IFNA関数やIFERROR関数とVLOOKUP関数を組み合わせて同様の処理を行う必要があります。

4. 「見つからない場合」に指定できる値

「見つからない場合」引数には、数値、文字列(ダブルクォーテーションで囲む)、または別のセル参照を指定できます。空白にする場合は、ダブルクォーテーションを2つ並べます(“”)。計算結果を返したい場合は、数式を指定することも可能ですが、複雑になりすぎないように注意が必要です。

XLOOKUP関数とVLOOKUP関数の比較

XLOOKUP関数が登場する以前は、VLOOKUP関数が一般的に使用されていました。両者の主な違いと、「見つからない場合」の処理について比較します。

項目	XLOOKUP関数	VLOOKUP関数
検索方向	左から右、右から左、上から下、下から上	左から右のみ
結果列の指定	結果範囲を指定	列番号を指定
「見つからない場合」の処理	「見つからない場合」引数で直接指定可能	IFNA関数やIFERROR関数と組み合わせる必要あり
デフォルトの検索モード	完全一致(近似一致も指定可能)	近似一致(完全一致はFALSEまたは0を指定)
必須引数	検索値、検索範囲、結果範囲	検索値、範囲、列番号
利用可能バージョン	Microsoft 365、Excel 2021以降	Excel 2007以降

VLOOKUP関数で「見つからない場合」の処理を行うには、IFNA関数やIFERROR関数を併用するのが一般的でした。例えば、以下のような記述になります。

VLOOKUP関数とIFNA関数を組み合わせた例:

=IFNA(VLOOKUP(A2,商品リスト!$A:$B,2,FALSE),”該当なし”)

XLOOKUP関数では、このIFNA関数の部分が「見つからない場合」引数に置き換わるため、よりシンプルで分かりやすい数式になります。

まとめ

XLOOKUP関数の「見つからない場合」引数を活用することで、検索結果で#N/Aエラーが表示されるのを防ぎ、より見やすく、管理しやすいExcelシートを作成できます。この引数を使えば、エラー箇所を「0」や特定のメッセージ、空白などに置き換えられるため、データ分析やレポート作成の精度が向上します。

ぜひ、XLOOKUP関数を使用する際には、「見つからない場合」引数を積極的に活用し、エラー表示を自在に制御してみてください。これにより、IFNA関数やIFERROR関数を別途記述する手間が省け、数式が簡潔になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】