XLOOKUPで同じIDが二行あるとどっち？最初・最後と未一致を分けよう

IDから金額を取り出すXLOOKUPを使ったら、同じIDの別の行が返ってきた。IDが重複する表では、どこから探すかも結果に関わるよ。

このページはGoogleスプレッドシートの数式を入力できる人向け。普通のシート上のXLOOKUPを使い、完全一致・検索の向き・未一致を架空の三行で比べる。BigQuery向けのXLOOKUPとは分けて扱うよ。

三行の検索表を用意する

A1にID、B1に金額と入力し、A2:B4を次にする。

この表ではA101が二回ある。返る金額が違うので、先に「一つだけ選ぶのか」「重複をエラーとして確認するのか」を決めたいね。XLOOKUPを置くだけで、IDが一意であることは保証されないよ。

完全一致で先頭から探す

E2にA101を入力し、F2へ次の数式を入れる。

=XLOOKUP(E2, A2:A4, B2:B4, "未登録", 0, 1)

検索キーはE2、探す場所はA2:A4、取り出す場所はB2:B4。第五引数の0は完全一致、第六引数の1は先頭から末尾へ探す指定だよ。

この条件なら、最初のA101に対応する100が結果になる。大きい金額を選んだり、二行を合計したりする関数ではないんだ。

末尾から探すなら検索モードを-1へ

=XLOOKUP(E2, A2:A4, B2:B4, "未登録", 0, -1)

この例では最後のA101に対応する350になる。第五引数の0はそのままで、第六引数の検索の向きだけを変えているよ。

最後の行が必ず最新の記録とは限らない。更新日時で決めたいなら、その日時と並び順の条件を別に揃えよう。表を並べ替えると、先頭・末尾に当たる行も変わるよ。

未一致と、ほかのエラーを混ぜない

第四引数の未登録は、完全一致するIDが見つからない場合の表示。第四引数を省略した場合の既定は#N/Aだよ。

これは表のすべての不具合を隠すための設定ではない。範囲の大きさが揃っていないなど、別の原因まで未登録と考えないでね。今回の縦方向の例では、検索範囲と結果範囲の行数を同じにしている。

また、空欄の金額や0という結果を、IDが見つからないことと同じに扱わないようにしよう。まず一致した行があるか、次に取り出す値は何かを確認したいね。

数字の引数を取り違えない

一致モードの-1と、検索モードの-1は役割が違う。ここで使ったのは第六引数の-1だけ。第五引数は完全一致の0に固定しているので、近いIDへ自動で置き換える例ではないよ。

検索モードには並べ替え条件が必要な別の値もある。数式を速そうな値へ変更する前に、公式の前提を確認しよう。この例では先頭・末尾の二方向だけを扱っている。

練習を終えるときは、出力側のF列の式と練習用の検索キーだけを消し、検索表は確認用に残せる。実データのIDを消して試す必要はないよ。

2026年10月29日にGoogle公式の完全一致・検索方向・未一致の条件を確認し、この三行の参照結果を独立のスクリプトで照合した。実際のSheets画面や数式を実行したとの主張ではないよ。UNIQUEの比較単位、IFERRORとIFNAも関連するね。