XLOOKUPとIFを組み合わせると、「未入力なら検索しない」「検索結果の数値で表示を変える」といった処理ができます。IFは条件分岐、XLOOKUPは表から値を探す役割です。複数条件そのものを検索したい場合とは式を分けて考えると分かりやすくなります。
この記事の基本の式です。セル番地は、下で説明する例の表のものです。
=IF(D2="","",XLOOKUP(D2,$A$2:$A$4,$B$2:$B$4,"該当なし"))未入力なら空白、入力したらXLOOKUPで検索する
A2:A4に商品名、B2:B4に在庫数、D2に検索したい商品名を入力する例です。E2へ次の式を入力します。
| A列：商品名 | B列：在庫数 |
|---|---|
| 商品A | 12 |
| 商品B | 0 |
| 商品C | 5 |
D2が空白ならE2も空白、商品Aなら12、商品Bなら0、存在しない商品Xなら「該当なし」です。「0個」と「商品が登録されていない」を区別できます。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。
IFとXLOOKUPのどちらを外側に置く？
| 判定したいこと | 式の組み立て |
|---|---|
| 検索する前に入力の有無を判定 | IFの中にXLOOKUP |
| 検索した数値で判定 | XLOOKUPの結果をセルに出し、そのセルをIFで判定 |
| 検索値が見つからないときの表示 | XLOOKUPの第4引数 |
検索した在庫数が0なら「在庫なし」にする
E2に先ほどの検索結果を表示し、F2で判定すると、式の役割が分かれ、どこで問題が起きたか確認できます。B列の在庫数には数値を入力しておきます。
在庫10個以下を「要発注」にする場合も、未登録を先に分けます。
▶ 在庫なしの判定（F2）を、練習ツールで試す ／ 要発注の判定（G2）を、練習ツールで試す
商品名とサイズの2条件で検索する
今度はA2:A5＝商品名、B2:B5＝サイズ、C2:C5＝在庫数、E2＝検索商品、F2＝検索サイズとします。入力漏れをIFで確認し、両条件を満たす行をXLOOKUPで探します。
IFERRORと「見つからない場合」の違い
XLOOKUPで見つからない場合の値を指定する方法では、空白・0・メッセージの使い分けを説明しています。検索成功後に空白セルが0になる問題は、XLOOKUPで本当の0を残し、空白を返す方法をご覧ください。
【実機で検証】空のセルと0、エラーの隠れ方
XLOOKUP と IF を組み合わせるときに間違えやすい点を、Excel の実機で試しました。
| 探した先 | =XLOOKUP(…) | IF(…=””) | IF(…=0) |
|---|---|---|---|
| 空のセル | 0 と表示 | 空と判定 | 0 と判定 |
| 本当の 0 | 0 | 空ではない | 0 と判定 |
未入力と在庫 0 を分けるなら「=””」で調べます。「=0」では、空のセルも本当の 0 も同じになりました。
IFERROR は指定ミスまで隠す
検索範囲と戻り範囲の行数を間違えた式で比べました。IFERROR で囲むと「なし」と表示され、ミスに気付けません。XLOOKUP の第4引数なら #VALUE! が出ます。見つからないときの表示は、第4引数で指定するのが安全です。
XLOOKUPとIFの式がうまく判定できない・エラーになるときの確認表
| 症状 | 主な原因 | 確認と対処 |
|---|---|---|
| #N/A や「該当なし」のままになる | 商品コードが数値と文字列で混在している／見えないスペースが付いている | 見た目が同じでも一致しません。検索値と一覧を、同じ形（数値か文字か）にそろえ、スペースを取り除く |
| #VALUE! になる | 検索範囲と戻り範囲の行数・開始行がずれている | 2つの範囲の開始行と終了行をそろえる |
| 数式の文字がエラーになる | 文字を全角の引用符で囲んでいる | 文字を直接書くときは、半角の ” で囲む |
| #NAME? になる | XLOOKUPが使えないExcel（2019・2016）で開いている | 検索部分をVLOOKUPやINDEX＋MATCHに置き換える |
検索範囲と戻り範囲の行数・開始行をそろえます。商品コードが数値と文字列で混在していたり、見えないスペースが付いていたりすると一致しません。文字を数式に直接書くときは半角の " で囲みます。
仕様の確認：Microsoft公式・XLOOKUP関数
XLOOKUPで複数の条件を指定したいときは、XLOOKUPの複数条件の書き方（2つ・3つ以上・OR・行と列）で、すべてのパターンをまとめて解説しています。
コメント