この記事について
この記事は、生成AIを活用した自動生成フローで作成しています。Microsoft公式のXLOOKUP仕様を参照し、架空の備品台帳で重複キーを調べる方法を紹介します。

検証ステータス：📘 公式仕様を参照・Excel実機での操作は未実施

XLOOKUPで同じ商品コードが2件あるとき、どちらを返す？先頭と末尾を比べる

Excelの備品台帳で商品コードを入力し、XLOOKUPで保管場所を表示する。便利な仕組みですが、同じコードの行が二つあったら、どちらの保管場所が表示されるのでしょうか。最初の一件が正しいとは限りません。古い行と新しい行が混在している場合、見た目には正常な検索結果でも、現場では間違った棚を案内してしまうことがあります。

今回は架空の小さな表を使い、先頭から探す場合と末尾から探す場合を比べます。さらに、重複そのものを検出する式も組み合わせます。重要なのは「末尾を選べば最新になる」と思い込まないこと。行の並びと更新日時の意味を分けて考えると、検索式の使いどころが見えてきます。

対象環境と準備

まず、新規ブックのA1:C5に次の表を入力してください。既存の台帳や会社の個人情報は使いません。日付はExcelの日付値として入力し、見た目だけの文字列にならないよう注意します。

検索するコードをE2に「PC-01」と入力します。F2には次の式を入力します。

=XLOOKUP(E2,A2:A5,B2:B5,"見つかりません")

この式は、A列からE2のコードを探し、同じ位置にあるB列の値を返します。公式仕様に基づく期待結果は「倉庫A」です。A2とA4の両方が一致していても、既定では先頭から検索し、最初に見つかった値を返すからです。式がエラーにならず、きれいな文字列を表示することこそ、この問題の見落としやすい点です。

1条件だけ変える：最後の一致を返す

次にG2へ以下の式を入力します。F2と違うのは、末尾の検索モードだけです。

=XLOOKUP(E2,A2:A5,B2:B5,"見つかりません",0,-1)

期待結果は「倉庫C」です。第5引数の0は完全一致を表し、第6引数の-1が末尾から先頭へ検索する指定です。既定の検索モード1とは向きが逆になります。表の最初と最後を比べるだけなので、並べ替えや削除をする必要はありません。

図は検索方向だけを示します。行の更新日を評価する仕組みではない点が重要です。

flowchart TD
    A["検索コード PC-01"] --> B{"検索モード"}
    B -->|"既定の1：先頭から"| C["A2を最初に発見"]
    B -->|"-1：末尾から"| D["A4を最初に発見"]
    C --> E["倉庫Aを返す"]
    D --> F["倉庫Cを返す"]

上から探せばA2、下から探せばA4が先に見つかります。同じ「一致」という判定でも、検索を始める場所が違うだけで結果が変わります。末尾検索は最も新しい日付を計算する機能ではありません。 たまたま今回の表で新しい日付の行が下にあるだけです。

重複を数えてから検索結果を読む

検索結果だけでは、コードが一つしかないのか、複数の候補から選ばれたのか分かりません。H2にCOUNTIFを追加します。

=COUNTIF(A2:A5,E2)

期待結果は2です。F2とG2に異なる保管場所が出た理由が、コードの重複にあると確認できます。コードが一件だけなら、先頭検索と末尾検索の結果は同じになります。コードが見つからなければCOUNTIFは0になり、今回のXLOOKUPでは「見つかりません」と表示されます。

業務用の台帳なら、重複が許されるかどうかを先に決めるべきです。商品マスターのようにコードが一意であるはずなら、重複を警告して修正する方が安全です。一方、履歴表のように同じコードの複数行が正常なら、どの時点の情報を選ぶかというルールが必要になります。

警告表示の例としてI2には次の式を使えます。検索結果の横に件数を置けば、値が出たことだけで安心せずに済みます。

=IF(H2=0,"未登録",IF(H2=1,"一意","重複あり：要確認"))

この式はデータを修正しません。あくまで確認のための目印です。複数行を一つに統合したり、古い行を削除したりする操作は別の判断として残します。

並び順を変えるとどうなるか

ここで、A2:C5の表全体を更新日の降順に並べ替えたとします。するとPC-01の新しい行が上側に移動し、先頭検索が「倉庫C」を返す可能性があります。逆に末尾検索は「倉庫A」を返すようになります。検索式自体を変えていなくても、表の並びが変われば答えが変わるのです。

この比較から分かるのは、「最後にある行」と「更新日が最大の行」は別の条件だということです。毎回必ず日付が昇順に並ぶことを業務手順で保証できるなら末尾検索は簡潔です。しかし、手作業で行を追加したり、他部署から受け取ったCSVを結合したりする台帳では、順番の保証が崩れやすくなります。

日付が最新のレコードを確実に選びたいなら、更新日で並べ替えた表を作ってから検索する、または更新日の最大値とコードの組み合わせで候補を絞る、といった設計が必要です。同じ日に複数回更新された場合の優先順位も決めてください。XLOOKUPの検索方向だけで履歴の正しさを保証することはできません。

失敗しやすい条件を切り分ける

式を入れても「見つかりません」になる場合、まずE2とA列のコードが本当に同じか確認します。前後の空白、全角・半角、見えない文字の混入は検索結果に影響します。見た目が似ているだけで一致しているとは限りません。入力規則でコードを選択させる方法や、データ取込時に表記を統一する方法も検討できます。

もう一つの注意は検索範囲です。A2:A5とB2:B5は対応する大きさでなければなりません。片方だけ範囲を伸ばした場合、想定外の結果やエラーの原因になります。実際の台帳が増え続けるなら、Excelテーブルに変換し、構造化参照を使う方法も候補です。ただしテーブル化しても、コードの重複が自動的に解消されるわけではありません。

また、今回の式は完全一致を指定しています。検索モード-1と、近似一致を指定する引数は役割が異なります。コードや社員番号など、少し違う値を別物として扱う識別子では、近似一致を安易に使わないでください。データの型が数値と文字列で混在している場合も、元データの型を確認してから式を直します。

業務での使い分け

備品マスターのようにコードが一つだけであるべき表では、COUNTIFで重複を発見し、管理者が元データを修正するのが基本です。検索方向を変えて見かけ上の正解を返すことは、重複の問題を隠す場合があります。反対に、問い合わせ履歴や価格改定履歴のように同じキーが複数回現れる表では、記録順が保証されるかを確認したうえで末尾検索を利用できます。

実務で便利なのは、検索結果と重複件数をセットで見せることです。「倉庫C」とだけ表示するより、「倉庫C／候補2件」と表示した方が、担当者は再確認すべきか判断できます。重要な処理では、重複があるときに自動確定させず、更新日と担当部署を見て選択する運用も考えられます。

今回の発見は、XLOOKUPが重複をエラーとして知らせる関数ではないという点です。先頭と末尾を切り替えることはできますが、どちらが正しいかは業務ルールで決まります。小さな練習表で検索方向を試してから、実データの並び順と一意性の条件を確認しましょう。

公式資料

• Microsoft サポート：XLOOKUP 関数

• Microsoft サポート：COUNTIF 関数

※掲載した結果は公式仕様に基づく期待値です。Excel実機での実行結果ではありません。