if_not_foundで見つからない場合に対応する

XLOOKUPで指定した値が見つからない場合はどうなるでしょうか。デフォルトでは#N/Aエラーが返されます。

このエラーは技術的には正しいものですが、レポート上では見栄えが悪く、結果を使う他の数式に影響することがあります。XLOOKUPには、この状況をきれいに処理する組み込みの方法があります。

XLOOKUPには、if_not_foundというオプションの4番目の引数があります。一致する値がない場合は、そこに指定した内容が返されます。

これは、IFERRORでラップする必要があったVLOOKUPに比べて大きな進歩です。XLOOKUPでは、代替値を同じ関数の中に指定できます。

最も一般的な使い方は、#N/Aの代わりに読みやすいテキストを表示することです。

ここでは、D2の商品がリストにない場合、セルにはエラーコードではなく「見つかりません」と表示されます。シートを読む人は、何が起きたのかをすぐに理解できます。

特に結果を計算に使う場合は、テキストよりも数値のほうが便利なことがあります。

一致するものがない場合に0を返せば、その後のSUMや乗算も独自のエラーを発生させずに動作します。

一致するものがないときにセルを見た目上空にするには、2つの引用符で囲んだ空のテキスト文字列を返します。

これは、プレースホルダーテキストよりも空白セルのほうが適している、すっきりしたダッシュボードで便利です。ただし、セルが本当に空というわけではなく、空の文字列が入っている点に注意してください。ほかの数式で空白かどうかを判定するときに、この違いが重要になります。

営業担当者が顧客名を検索するために、D2に注文IDを入力するとします。注文IDはA列、名前はC列に入っています。

IDを入力し間違えた場合、数式は分かりにくいエラーではなく、明確な指示を返します。フォールバックメッセージによって、入力を修正するよう促せます。

if_not_foundの値は、テキストを直接入力する必要はありません。別のセルを参照することもできます。

たとえば、G1に既定の地域を保存し、特定の検索に失敗したときは常にその値を返すようにできます。これなら、数式を変更せずにフォールバックの値だけを編集できます。

XLOOKUPをIFERRORで囲むこともできますが、微妙な違いがあります。

• if_not_foundは、一致するものがない場合だけを処理します
• IFERRORは、数式そのものの本当のミスが原因のエラーも含め、すべてのエラーを隠します

if_not_foundを使うほうが安全です。本当のバグが隠されず、見える状態に保たれるためです。

便利な方法として、1つのXLOOKUPのフォールバックに別のXLOOKUPを指定できます。

まず主要なリストを検索し、項目が見つからなければバックアップのリストを検索します。IFERRORを入れ子にするよりもはるかにすっきりしており、ほぼ自然な文章のように読めます。

検索結果を計算に使う場合は、数値のフォールバックにすると全体が正常に動作します。

ここでは、価格が見つからない場合に0を返すため、E2の数量との乗算も、シート全体にエラーが広がることなく数値を返します。

セルの目的に合ったフォールバックを選んでください。

• 人が読むレポートには「見つかりません」のようなテキスト
• 後で値を加算または乗算する場合は0
• 見た目をすっきりさせるには空の文字列
• 複数のデータソースを扱う場合は別の検索

4番目の引数を使うと、壊れやすい検索結果を洗練されたプロフェッショナルな結果に変えられます。

検索で何も見つからない場合の処理方法を学びました。

• XLOOKUPは、一致するものがない場合、デフォルトで#N/Aを返します
• 省略可能なif_not_found引数で、適切なフォールバックを指定できます
• テキスト、0、空白、セル、さらには2つ目のXLOOKUPも指定できます
• 一致しない場合だけを処理するため、本当のエラーは見える状態に保たれます

次は、リストを左方向から検索したり、下から検索したりする方法を学びます。