XLOOKUPだけでOK　縦横検索の基本と実例（IFERRORは使わない）

XLOOKUPが使えるか環境を確認する

XLOOKUPは、Microsoft 365のExcelや一部の最新版のExcelに入っている関数です。会社や学校のパソコンでも、更新のタイミングにより使えるかどうかが変わることがあります。まずは自分の環境で関数名を入力して候補に出るかを確認します。数式バーで「=XLO」と打ち、候補にXLOOKUPが表示されれば使えます。

Excel 2016以前の買い切り版では、XLOOKUPがない場合があります。そのときは、従来のVLOOKUPやINDEX/MATCHで対応します。Excel for web（ブラウザー版）でも、組織の設定により提供状況が変わることがあります。管理者が更新を止めていると、表示できても計算できないことがあります。

Googleスプレッドシートでは、同名のXLOOKUPが用意されています。ただし、引数の並びや動きがExcelと少し違う点があります。この記事ではExcelのXLOOKUPを前提に説明し、必要に応じてスプレッドシートの注意点も添えます。

社内でブックを共有する場合は、編集者の環境も確認しましょう。XLOOKUPがない人が開くと、関数名が不明としてエラーになることがあります。共有前に、参照を値貼り付けして渡す、または関数をVLOOKUPに置き換えると安心です。

ミニQA：XLOOKUPが候補に出ない場合のチェック手順は？

1. Excelのバージョンを確認します。アカウント画面で更新チャネルとバージョン情報を見ます。
2. 会社PCなら管理部門に更新状況を確認します。更新停止中のことがあります。
3. ブラウザー版なら、別のブラウザーで試すか、デスクトップ版で開いて確認します。
4. 使えない場合は、同じ処理をVLOOKUPやINDEX/MATCHで代替します。

XLOOKUPの基本構文と引数の役割

基本の形は次のとおりです。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない時, 一致のしかた, 探す向き)

引数は左から順に、次の意味です。

• 検索値：探したい値。セル参照や文字列、数値を入れます。
• 検索範囲：検索値を探す列または行。1列または1行が基本です。
• 戻り範囲：見つかったときに返す列または行。検索範囲と同じサイズでそろえます。
• 見つからない時：該当がない場合に表示したい文字列や数値。空白にしたい時は””を入れます。
• 一致のしかた（match_mode）：完全一致か、近い値を許すかを指定します。
• 探す向き（search_mode）：先頭から探すか、末尾から探すか、または二分探索を使うかを選びます。

XLOOKUPは縦方向（列）でも横方向（行）でも同じ構文で使えます。VLOOKUPと違い、戻り列の位置番号を気にする必要がありません。戻り範囲を直接指定するため、列の挿入・削除で式が壊れにくいのが利点です。

よく使う最小構成は、見つからない時と一致のしかたを省略し、完全一致で探す形です。

=XLOOKUP(A2, 商品コード列, 単価列)

ここで、A2が検索値、商品コード列が検索範囲、単価列が戻り範囲です。完全一致が前提なので、該当がなければエラーになります。エラー表示を変えたいときは、4番目の引数に表示したい文字列を指定します。

=XLOOKUP(A2, 商品コード列, 単価列, “未登録”)

「一致のしかた（match_mode）」は次の選択肢があります。

• 0：完全一致
• -1：以下で最も近い（検索値を超えない最大の値）
• 1：以上で最も近い（検索値未満を許さない最小の値）
• 2：ワイルドカード一致（*や?を使う）

「探す向き（search_mode）」は次の通りです。

• 1：先頭から順に探す（既定）
• -1：末尾から逆向きに探す
• 2：昇順に並んだ範囲で高速に探す（二分探索）
• -2：降順に並んだ範囲で高速に探す（二分探索）

ミニQA：真ん中の列や行でも検索できますか？

できます。検索範囲と戻り範囲を直接指定するため、列の位置番号を指定する必要がありません。たとえば、商品名（B列）からコード（A列）を返すことも可能です。

完全一致を確実にする基本パターン

最も安全なのは完全一致です。誤った近似値を避け、データの正確性を保てます。完全一致は「一致のしかた」を0にするか、省略すると完全一致になります。

基本の式：

=XLOOKUP(A2, A:A, D:D)

見つからない時に空白を返したい場合：

=XLOOKUP(A2, A:A, D:D, “”)

見つからない時に文言を返したい場合：

=XLOOKUP(A2, A:A, D:D, “未登録”)

ワイルドカードを使った部分一致は、「一致のしかた」を2にし、検索値に*や?を含めます。

=XLOOKUP(“*”&E2&”*”, B:B, D:D, “該当なし”, 2)

この式は、B列の文字列にE2の語を含む行を探し、D列の値を返します。複数候補がある場合は最初に見つかった行が返ります。末尾の方を優先したいなら「探す向き」を-1にします。

=XLOOKUP(“*”&E2&”*”, B:B, D:D, “該当なし”, 2, -1)

ミニQA：見つからないときだけ空白や「未登録」を出す式は？

「見つからない時」の引数に空文字””や文言を入れます。例：=XLOOKUP(A2, A:A, D:D, “未登録”)。

VLOOKUPの「近似値」をXLOOKUPで再現する

VLOOKUPの近似値は、範囲の左端列を並べ替えた上で、検索値以下の最大を返すのが基本でした。XLOOKUPでは「一致のしかた」と「探す向き」で同等以上の制御ができます。

検索値以下で最も近い値（VLOOKUPの近似値に相当）：

=XLOOKUP(G2, しきい値列, 結果列, , -1)

検索値以上で最も近い値：

=XLOOKUP(G2, しきい値列, 結果列, , 1)

二分探索で高速化したいときは、範囲を並べ替えた上で「探す向き」を2（昇順）または-2（降順）にします。

昇順に並んだ範囲で以下で最も近い：

=XLOOKUP(G2, しきい値列, 結果列, , -1, 2)

降順に並んだ範囲で以上で最も近い：

=XLOOKUP(G2, しきい値列, 結果列, , 1, -2)

小さな例で考えます。左の表で「購入金額」に応じて「送料」を決めるルールを作ります。

セルH2に購入金額が入っているとします。以下で最も近い送料は次の式で求められます。

=XLOOKUP(H2, A2:A4, B2:B4, , -1)

この式は、H2の金額を超えない最大のしきい値を探し、その行の送料を返します。表が昇順で並んでいればそのままで問題ありません。

注意点として、二分探索（2や-2）を使う場合は、並べ替え順が合っていないと誤った結果になります。順序が不確かな表では、既定の探索（1または-1）を使い、並べ替えを気にしない設定のほうが安全です。

ミニQA：「検索値以下の一番近い値」を返す式は？

=XLOOKUP(検索値, しきい値列, 戻り列, , -1)です。昇順で並べ替え済みで高速化したいときは、最後に2を加えます。

関連情報をまとめて取り出す（担当者・部署など）

同じキーから、担当者名や部署名など複数の列を取り出したい場面があります。XLOOKUPは戻り範囲を1列ずつ指定するのが基本です。同じ検索値を使う列では、式の参照を固定してコピーすると効率的です。

例：A列に商品コード、C列に担当者、D列に部署があるとします。B列に単価、E列に担当者、F列に部署を表示したい場合は次のようにします。

単価：=XLOOKUP(A2, A:A, B:B, “未登録”)

担当者：=XLOOKUP(A2, A:A, C:C, “未登録”)

部署：=XLOOKUP(A2, A:A, D:D, “未登録”)

A2の検索値部分だけ相対参照にして、検索範囲と戻り範囲は絶対参照にすると、下方向にコピーしても範囲がずれません。テーブル機能（Ctrl+T）を使うと、列名で参照でき、範囲固定の手間が減ります。

複数の戻り列を一度に取り出したい場合は、動的配列を使って横にスピルさせる方法があります。戻り範囲を連続列で指定し、式を1つだけ入力します。

=XLOOKUP(A2, テーブル[コード], テーブル[{単価, 担当, 部署}], “未登録”)

ミニQA：列が増えた時に式を一括で流用する方法は？

テーブル機能を使い、列名で参照します。新しい列をテーブルに追加すれば、既存の式の戻り範囲に列名を追加するだけで対応できます。

重複のない一覧作りと強調表示（UNIQUE＋条件付き書式）

データから重複のない一覧を作り、元表の該当セルを塗りつぶすと、配布先や担当者の抜け漏れを見つけやすくなります。まずUNIQUEで一意の一覧を作ります。

=UNIQUE(担当者列)

できた一覧を別の列に表示し、その一覧に含まれる担当者が元表のどこにあるかを条件付き書式で強調します。セルの強調には、COUNTIFで一覧に含まれるかを判定する方法が分かりやすいです。

条件付き書式の数式例：

=COUNTIF($J:$J, $C2)>0

ここで、J列にUNIQUEで生成した担当者一覧、C列が元表の担当者列です。TRUEになったセルに塗りつぶし色を設定します。

一覧をプルダウン（データの入力規則）に使う場合は、UNIQUEの範囲をそのまま規則に指定します。新しい担当者が追加されると、プルダウンの候補も自動で増えます。

ミニQA：UNIQUEで作った一覧をデータ検証のプルダウンに使えますか？

使えます。入力規則の範囲にUNIQUEの結果範囲を指定します。テーブルと組み合わせると更新が自動で反映されます。

並べ替え表示はSORTとSORTBYを使い分け

表示だけ別表に並べ替えたいときは、SORTとSORTBYを使います。元表を壊さずに、別の順番で見せることができます。

• SORT：範囲全体を、指定した列や行のキーで並べ替えて表示します。
• SORTBY：別の列や式をキーにして並べ替えます。複数キーや、集計列での並べ替えが得意です。

昇順・降順は、引数で1（昇順）または-1（降順）を選びます。複数のキーを使う場合は、SORTBYを選ぶと式が分かりやすくなります。

例：売上表を担当者の降順、同点は商品コードの昇順で表示する。

=SORTBY(売上テーブル, 売上テーブル[担当者], -1, 売上テーブル[コード], 1)

ミニQA：特定列を優先して並べ替えるにはどちらを使う？

複数キーや計算列を使うならSORTBY、単純に1列のキーで並べたいだけならSORTが手軽です。

IFERRORが不要な理由と安全なエラー制御

XLOOKUPは4番目の引数「見つからない時」で、未登録や空白などの表示を直接制御できます。これにより、IFERRORでエラー全体を包む必要がないことが多いです。IFERRORは、計算式全体のあらゆるエラーを隠してしまうため、思わぬ不具合を見落とす原因になります。

例：未登録時だけ”未登録”と出したい場合

=XLOOKUP(A2, A:A, D:D, “未登録”)

この形なら、参照切れや型の不一致など別種のエラーは表に出るため、早く気づけます。一方、IFERRORで全体を包むと、見つからない以外のエラーも同じ文言に置き換わり、原因調査が遅れることがあります。

IFERRORを使うべき場面は、前段の計算が一時的にエラーになりやすいと分かっている場合など、限定的です。XLOOKUP単体の見つからないだけを制御したいなら、4番目の引数で十分です。

ミニQA：IFERRORを使った時の予期せぬ副作用は？

見つからない以外のエラー（参照切れ、ゼロ除算など）も同じ表示に置き換わります。結果として、異常に気づきにくくなります。

実務での置き換え手順とチェックリスト

VLOOKUPやHLOOKUPからXLOOKUPへ置き換えると、列挿入に強くなり、メンテナンスが楽になります。安全に移行するための手順をまとめます。

1. 置き換え対象の式を洗い出します。参照範囲と戻り列が固定番号になっている箇所を確認します。
2. 元表をテーブル化して列名参照にします。範囲指定のずれを防げます。
3. XLOOKUPで同じ結果が返るかを小さな範囲で検証します。完全一致から置き換え、近似値は別途テストします。
4. エラー表示はXLOOKUPの4番目の引数で設定します。IFERRORは使わず、原因の違いを見分けられる形にします。
5. シート全体に広げ、相互参照や循環参照が起きないかを確認します。

違いを一目で把握するために、主な関数を比較します。

移行後は、数式の読みやすさが上がり、保守の手間が減ります。特に、戻り列を追加したいときも、戻り範囲を変えるだけで対応できます。

ミニQA：旧ブックを壊さずに段階移行するステップは？

同じシートに複製列を作り、VLOOKUP版とXLOOKUP版を並べて結果を比較します。すべて一致したら、元の列をXLOOKUPに差し替えます。

付録：式サンプルのまとめ

完全一致で単価を返す：=XLOOKUP(A2, 商品[コード], 商品[単価], “未登録”)

検索値以下で最も近い送料：=XLOOKUP(H2, 送料表[金額], 送料表[送料], , -1)

末尾優先の部分一致：=XLOOKUP(“*”&E2&”*”, 顧客[名称], 顧客[担当], “該当なし”, 2, -1)

複数列を一度に返す（動的配列）：=XLOOKUP(A2, 商品[コード], 商品[{単価, 担当, 部署}], “未登録”)

注意事項

関数の提供状況や動作は、アプリの更新や組織設定で変わることがあります。ここでの式は一般的な例です。実際のブックに適用する際は、検証用のコピーで試してから本番データに反映してください。